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' <<EOF
set pages 1000 feedb off
spo ck_ts_fs.lst
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 < $PCTFREE_THRHLD  --%freespace, 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;
exit
EOF

if [[ $(wc -l ck_ts_fs.lst|awk '{print $1}') -le 3 ]]; then
  echo "All tablespaces are above $PCTFREE_THRHLD percent free!"
else
  echo "At least one tablespace has lower than $PCTFREE_THRHLD percent free space (even considering autoextension if configured)!"
  mail -s "Warning: Tablespace free space alert on $ORACLE_SID" $RECIPIENT < ck_ts_fs.lst
fi