Tablespace Free Space Check A tablespace free space check script is deceivingly simple. A common mistake is the case when the free space is so small even one extent can't be created in the tablespace so the tablespace name disappears from dba_free_space. Many scripts such as the one in Metalink Note:1019999.6, not using outer joins between dba_data_files and dba_free_space, or using outer joins but with a simple freespace > somepercentage without NVL, will miss those tablespaces that need space the most. Some shops use OEM (Oracle Enterprise Manager) to monitor tablespaces. But OEM relies on dba_tablespace_usage_metrics. That view (or rather, its base table x$kttets) has been buggy ever since 9i. It may be permanently corrected in 19c. If you don't use OEM or want to have an alternative space check script, read on. The most basic check is the following, if no datafile is autoextensible. The example SQL lists tablespaces whose usage exceeds 90%, and optionally only lists those with less than 1 GB free space. select a.tablespace_name, totalspace, nvl(freespace,0) freespace, (totalspace-nvl(freespace,0)) used, ((totalspace-nvl(freespace,0))/totalspace)*100 "%USED" from (select tablespace_name, sum(bytes)/1048576 totalspace from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(Bytes)/1048576 freespace from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name (+) and ((totalspace-nvl(freespace,0))/totalspace)*100 > 90 --and nvl(freespace,0) < 1000 -- only list TSs < 1GB free order by 5 desc; A more sophisticated one, if some datafiles are autoextensible, and you want to exclude read-only TS's and some other specific ones: select a.tablespace_name, cur_total, nvl(freespace,0) cur_free, (cur_total-nvl(freespace,0)) cur_used, (nvl(freespace,0)/cur_total)*100 "%CUR_FREE", --%freespace not considering autoextension max_can_ext2, --max this TS can extend to (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 "%MAX_FREE" --%freespace considering autoextension from (select tablespace_name, sum(bytes)/1048576 cur_total, sum(decode(maxbytes,0,bytes,greatest(maxbytes,bytes)))/1048576 max_can_ext2 --even if autoextensible, maxbytes may be < bytes from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes)/1048576 freespace from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name (+) and (nvl(freespace,0)+max_can_ext2-cur_total)/max_can_ext2*100 < 10 --freespace < 10%, considering autoextension and a.tablespace_name not in (select tablespace_name from dba_tablespaces where status = 'READ ONLY') and a.tablespace_name != 'AUDIT_DATA' -- this TS is checked manually order by 7; If the SQL runs slow, query of dba_free_space probably runs slow, too. Make sure recyclebin$ or dba_recyclebin is empty or near empty. That's the most common cause of the problem. Sometimes you fix it by gathering stats on sys.x$ktbue. Complete shell script ready for a cron job: #!/bin/bash #ck_freespace.sh: Check tablespace free space. export ORACLE_SID=orcl export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db export PATH=$ORACLE_HOME/bin:/usr/bin:/bin RECIPIENT=yong321,fellowdba PCTFREE_THRHLD=15 #percent free out of max size that can be extended to cd /u01/app/oracle/scripts/ck_ts_fs sqlplus -s -L '/ as sysdba' <