How to find out tablespaces with free space < 15%

SQL> set pagesize 300
SQL> set linesize 100
SQL> column tablespace_name format a15 heading 'Tablespace'
SQL> column sumb format 999,999,999
SQL> column extents format 9999
SQL> column bytes format 999,999,999,999
SQL> column largest format 999,999,999,999
SQL> column Tot_Size format 999,999 Heading 'Total Size(Mb)'
SQL> column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
SQL> column Pct_Free format 999.99 heading '% Free'
SQL> column Max_Free format 999,999,999 heading 'Max Free(Kb)'
SQL> column Min_Add format 999,999,999 heading 'Min space add (MB)'
SQL>
SQL> ttitle center 'Tablespaces With Less Than 15% Free Space' skip 2
SQL> set echo off
SQL>
SQL> select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
  2  sum(a.sumb/1024) Tot_Free,
  3  sum(a.sumb)*100/sum(a.tots) Pct_Free,
  4  ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
  5  from
  6  (
  7  select tablespace_name,0 tots,sum(bytes) sumb
  8  from dba_free_space a
  9  group by tablespace_name
 10  union
 11  select tablespace_name,sum(bytes) tots,0 from
 12  dba_data_files
 13  group by tablespace_name) a
 14  group by a.tablespace_name
 15  having sum(a.sumb)*100/sum(a.tots) < 15
 16  order by pct_free;

                         Tablespaces With Less Than 15% Free Space

Tablespace      Total Size(Mb) Total Free(Kb)  % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
SYSAUX                     500         24,448    4.78                 61
SYSTEM                     710         37,504    5.16                 83

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.