The following procedure will sum the space still to be claimed by the autoextensible datafiles per filesystem and compare those sums will the filesystems' free space. Be aware, this assumes there are no more than one database's autoextensible datafiles per filesystem. Otherwise, you may try to use database links to gather all required information in one database and get an aggregated result.
First of all, we need to retrieve information about the filesystems in our server, so since this is a Linux server, we will use the "df" command. In the home directory [/home/oracle], create a df.sh:
#!/bin/sh
/bin/df -Pl
And make it executable:
chmod u+x df.sh
Execute it:
Filesystem 1277608 73094 139296 3771552 227884 177541 177541 7751656 12874296
For instance, we can see /oradata filesystem has 866416 Kbytes of unused space.
Now, we need to make this information available to our database.
We create a directory to point to our home directory:
CREATE OR REPLACE DIRECTORY HOME_DIR AS '/home/oracle';
And an external table to query this information:
CREATE TABLE FILESYSTEMS
(FSNAME VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), MOUNT VARCHAR2(100))
ORGANIZATION EXTERNAL
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY HOME_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
NOLOGFILE
PREPROCESSOR
HOME_DIR:'df.sh'
SKIP 1
FIELDS TERMINATED BY WHITESPACE LDRTRIM)
LOCATION
(HOME_DIR:'df.sh'));
Let's use it:
SELECT * FROM FILESYSTEMS;
| FSNAME | BLOCKS | USED | AVAILABLE | CAPACITY | MOUNT |
| /dev/mapper/rootvg00-lvol_root | 18368380 | 5043772 | 12391540 | 29% | / |
| tmpfs | 1961068 | 683460 | 1277608 | 35% | /dev/shm |
| /dev/sda1 | 198337 | 73094 | 115003 | 39% | /boot |
| /dev/mapper/rootvg00-lvol_home | 2064208 | 137952 | 1821400 | 8% | /home |
| /dev/mapper/rootvg00-lvol_tmp | 4128448 | 139296 | 3779440 | 4% | /tmp |
| /dev/mapper/rootvg00-lvol_var | 8260812 | 3772356 | 4068824 | 49% | /var |
| /dev/mapper/rootvg00-lvol_vartmp | 4128448 | 227952 | 3690784 | 6% | /var/tmp |
| /dev/mapper/OracleVG-lvbinaries | 11123304 | 6210100 | 4348164 | 59% | /orabin |
| /dev/mapper/OracleVG-lvredo2 | 444276 | 177541 | 243798 | 43% | /redo2 |
| /dev/mapper/OracleVG-lvredo1 | 444276 | 177541 | 243798 | 43% | /redo1 |
| /dev/mapper/OracleVG-lvarch | 21778916 | 197192 | 243798 | 1% | /orarch |
| /dev/mapper/OracleVG-lvdata | 9079280 | 7751656 | 866416 | 90% | /oradata |
| /dev/mapper/vgoradata2-lvoradata2 | 20428668 | 6516652 | 12874296 | 34% | /oradata2 |
We create one more table [FILESYSTEMS_INFO], identical to FILESYSTEMS, plus 2 more columns: TO_BE_USED, where we will sum the space still to be claimed by the autoextensible datafiles from DBA_DATA_FILES and DBA_TEMP_FILES, and OK, which will get value 'YES' if TO_BE_USED<AVAILABLE and 'NO' otherwise.
CREATE TABLE FILESYSTEMS_INFO
(FSNAME VARCHAR2(100), MOUNT VARCHAR2(100), BLOCKS NUMBER, USED NUMBER, AVAILABLE NUMBER, CAPACITY VARCHAR2(5), TO_BE_USED NUMBER, OK VARCHAR2(3))
TABLESPACE USERS;
We run the following PL/SQL code:
DECLARE
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE FILESYSTEMS_INFO';
INSERT INTO FILESYSTEMS_INFO(FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, TO_BE_USED, OK)
SELECT FSNAME, MOUNT, BLOCKS, USED, AVAILABLE, CAPACITY, 0, 'YES'
FROM FILESYSTEMS;
UPDATE FILESYSTEMS_INFO B SET (B.TO_BE_USED, B.OK)=
(SELECT NVL(SUM(A.MAXBYTES - A.USER_BYTES)/1024, 0), CASE WHEN SUM(A.MAXBYTES - A.USER_BYTES)/1024 >= B.AVAILABLE THEN 'NO' ELSE 'YES' END
FROM (SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_DATA_FILES UNION SELECT FILE_NAME, AUTOEXTENSIBLE, MAXBYTES, USER_BYTES FROM DBA_TEMP_FILES) A
WHERE A.AUTOEXTENSIBLE = 'YES'
AND INSTR(A.FILE_NAME, B.MOUNT||'/', 1, 1) = 1);
COMMIT;
END;
/
Now, we query FILESYSTEMS_INFO:
SELECT MOUNT, ROUND(AVAILABLE/1024/1024, 2) "AVAILABLE GB", ROUND(TO_BE_USED/1024/1024, 2) "TO BE USED GB", OK
FROM FILESYSTEMS_INFO;
| MOUNT | AVAILABLE GB | TO BE USED GB | OK |
| / | 11,82 | 0 | YES |
| /dev/shm | 1,22 | 0 | YES |
| /boot | 0,11 | 0 | YES |
| /home | 1,74 | 0 | YES |
| /tmp | 3,6 | 0 | YES |
| /var | 3,88 | 0 | YES |
| /var/tmp | 3,52 | 0 | YES |
| /orabin | 4,15 | 0 | YES |
| /redo2 | 0,23 | 0 | YES |
| /redo1 | 0,23 | 0 | YES |
| /orarch | 19,57 | 0 | YES |
| /oradata | 0,83 | 0,63 | YES |
| /oradata2 | 12,28 | 35,09 | NO |
/oradata filesystem is OK, because it has 0,83 GBytes of free space and all autoextensible datafiles in it could claim 0,63 GBytes maximum.
On the other hand, /oradata2 has 12,28 GBytes of free space and 35,09 GBytes could be claimed. We should decrease one or more autoextensible datafiles' MAXSIZE.
