Procedure to automate datafiles' resizing taking into account filesystems' free space

I wanted to create a mechanism for our operators to be able to give more space to tablespaces, automatically, when there is a need to.
The operators don't know the datafiles' location and the filesystems' free space, so the only thing they will do is to run a schell script with the tablespace name as a parameter.

Unfortunately, oracle database does not store any information about the filesystems and their space usage, so if you are not careful you may end up with completely full filesystems.
We can only get this info, using OS commands like bdf and df, so to make it available to our database, we will create a text file in our server, containing the output of those commands and an external table in the database to read that file.

I created this example in HP-UX, so you will need to do a few modifications to make it run in Linux or other Unix.
For example, bdf is an HP-UX command and you should use df in other Unix/Linux.

First, create a directory pointing to the location, where our scripts and output files will be stored, e.g. /oradb/orabin/scripts:

CREATE DIRECTORY SCRIPT_DIR AS '/oradb/orabin/scripts';

Now, create the external table, called df, which reads /oradb/orabin/scripts/bdf.txt:

CREATE TABLE DF
                   (FILESYSTEM VARCHAR2(200),
                    KBYTES       NUMBER,
                    USED         NUMBER,
                    AVAIL        NUMBER,
                    USED_PER     VARCHAR2(20),
                    MOUNTED      VARCHAR2(100)
                   )
     ORGANIZATION EXTERNAL
     (
       TYPE ORACLE_LOADER
       DEFAULT DIRECTORY SCRIPT_DIR
       ACCESS PARAMETERS
       (
         RECORDS DELIMITED BY NEWLINE
         BADFILE SCRIPT_DIR:'bdf%a_%p.bad'
         LOGFILE SCRIPT_DIR:'bdf%a_%p.log'
         FIELDS TERMINATED BY ' '
         MISSING FIELD VALUES ARE NULL
       )
       LOCATION ('bdf.txt')
     )
     REJECT LIMIT UNLIMITED;

Our script will make some of the given tablespace's datafiles autoextensible, taking into account the filesystems' free space.
We discard any datafiles there are in filesystems with > 99% of used space.
For all others, we autoextend them to a size equal to:
CURRENT DATAFILE SIZE + 10% OF FILESYSTEM'S FREE SPACE
If the above size is greater than tablespace's block size * 4194302, then we set MAXSIZE UNLIMITED, to avoid to get an ORA error.
So, create file /oradb/orabin/scripts/extend.sql:

SET SERVEROUTPUT ON
SPOOL OUT.TXT

DECLARE
   V_STMT    VARCHAR2 (1000);
   V_BSIZE   NUMBER;

   CURSOR C1
   IS
      SELECT A.FILE_NAME,
             A.BYTES,
             B.AVAIL,
             (BYTES / 1024) + (AVAIL * 0.1) NEW_SIZE
        FROM DBA_DATA_FILES A, DF B
       WHERE     A.TABLESPACE_NAME = UPPER ('&1')
             AND SUBSTR (A.FILE_NAME, 1, INSTR (A.FILE_NAME, '/', -1, 2) - 1) = B.MOUNTED
             AND SUBSTR (B.USED_PER, 1, INSTR (B.USED_PER, '%') - 1) < 99;
BEGIN
   SELECT BLOCK_SIZE
     INTO V_BSIZE
     FROM DBA_TABLESPACES
    WHERE TABLESPACE_NAME = UPPER ('&1');

   FOR DFILE IN C1
   LOOP
      DBMS_OUTPUT.PUT_LINE ('Filename: ' || DFILE.FILE_NAME);
      DBMS_OUTPUT.PUT_LINE ('Size (KB): ' || DFILE.BYTES / 1024);
      DBMS_OUTPUT.PUT_LINE ('Avail (KB): ' || DFILE.AVAIL);

      IF DFILE.NEW_SIZE < V_BSIZE * 4194302 / 1024
      THEN
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE ' || DFILE.NEW_SIZE || 'K';
      ELSE
         V_STMT := 'ALTER DATABASE DATAFILE ''' || DFILE.FILE_NAME || ''' AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED';
      END IF;

      DBMS_OUTPUT.PUT_LINE (V_STMT);
      EXECUTE IMMEDIATE V_STMT;
      DBMS_OUTPUT.PUT_LINE ('--------------------------');
   END LOOP;
END;
/

SPOOL OFF

Finally, we create the shell script /oradb/orabin/scripts/extend.sh, which creates the bdf.txt with the appropriate format for our external table. It also sends an email to the DBAs, so they get informed that the procedure run and which datafiles were affected.

#!/bin/sh
ORACLE_HOME=/oradb/orabin/app/product/db1
export ORACLE_HOME
ORACLE_SID=OTE
export ORACLE_SID

rm bdf*
rm original.txt
bdf|grep -v File|grep -v vol|grep -v odm|grep -v shared>original.txt
sed -e "s/  */ /g" original.txt > bdf.txt

cd /oradb/orabin/scripts
sqlplus -s /nolog << EOF
connect / as sysdba
@extend.sql $1
exit
EOF

cat OUT.TXT|mailx -s "Autoextend script run for $ORACLE_SID" name@ote.gr

Now, the only thing the operator has to do is run extend.sh with the tablespace name as a parameter:

extend.sh user_data