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 OFFFinally, 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.grNow, the only thing the operator has to do is run extend.sh with the tablespace name as a parameter:
extend.sh user_data