SQL Script to display the table space report for the tablespaces with less than 15% free space.

The below script can be used to display the table space report for only those tablespaces which is 85% Full or in other words which has less than 15% free space.

col tablespace_name format a30 heading "Tablespaces|over 85% Full"
col total_space format 999,999,990.00 heading "Current|Total|Space (Gb)"
col free_space format 999,999,990.00 heading "Current|Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col addl_space format 999,990.00 heading "Total Space|after|AutoExtend"
col addl_free_space format 999,990.00 heading "Total Free|Space after|AutoExtend"
break on a1 on report
compute sum of a3 on report
compute sum of a4 on report
set termout off
set pause off
ttitle "View Free Tablespace Size by Free%"
set pages 50 lines 100
select  t.tablespace_name,
        s.total_space,
        f.free_space,
        (f.free_space / s.total_space) * 100 pct_free,
        s.extensible addl_space,
        f.free_space + (s.extensible - s.total_space) addl_free_space
from    dba_tablespaces                  t,
        (select         tablespace_name,
                        sum(user_bytes) / (1024*1024*1014) total_space,
                        sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / (1024*1024*1014) extensible
         from           dba_data_files
         group by       tablespace_name) s,
        (select         tablespace_name,
                        sum(bytes) / (1024*1024*1014) free_space
         from           dba_free_space
         group by       tablespace_name) f
where   t.contents = 'PERMANENT'
and     s.tablespace_name = t.tablespace_name
and     f.tablespace_name = t.tablespace_name
and     (f.free_space / s.total_space) * 100 <= 15
order by 4
/

Example o/p will be some thing like below:

Thu Oct 08                                                                                 page    1
                                 View Free Tablespace Size by Free%

                         Current         Current              Total Space      Total Free
Tablespaces                Total            Free Current            after     Space after
over 85% Full         Space (Gb)      Space (Gb)  % Free       AutoExtend      AutoExtend
----------       --------------- --------------- -------      -----------     -----------
RBS32                       0.59            0.05    9.10            32.32           31.78

And if we want to display the details of all existing tablespaces then just remove the line " and     (f.free_space / s.total_space) * 100 <= 15" from the script it will display the details of all the existing tablespaces in the database,

Script:

col tablespace_name format a30 heading "Tablespaces"
col total_space format 999,999,990.00 heading "Current|Total|Space (Gb)"
col free_space format 999,999,990.00 heading "Current|Free|Space (Gb)"
col pct_free format 990.00 heading "Current|% Free"
col addl_space format 999,990.00 heading "Total Space|after|AutoExtend"
col addl_free_space format 999,990.00 heading "Total Free|Space after|AutoExtend"
break on a1 on report
compute sum of a3 on report
compute sum of a4 on report
set termout off
set pause off
ttitle "View Free Tablespace Size by Free%"
set pages 50 lines 100
select  t.tablespace_name,
        s.total_space,
        f.free_space,
        (f.free_space / s.total_space) * 100 pct_free,
        s.extensible addl_space,
        f.free_space + (s.extensible - s.total_space) addl_free_space
from    dba_tablespaces                  t,
        (select         tablespace_name,
                        sum(user_bytes) / (1024*1024*1014) total_space,
                        sum(decode(autoextensible, 'YES', maxbytes, user_bytes)) / (1024*1024*1014) extensible
         from           dba_data_files
         group by       tablespace_name) s,
        (select         tablespace_name,
                        sum(bytes) / (1024*1024*1014) free_space
         from           dba_free_space
         group by       tablespace_name) f
where   t.contents = 'PERMANENT'
and     s.tablespace_name = t.tablespace_name
and     f.tablespace_name = t.tablespace_name
order by 4
/

Example Report:

Thu Oct 08                                                                                 page    1
                                 View Free Tablespace Size by Free%

                       Current         Current           Total Space  Total Free
                         Total            Free   Current       after Space after
Tablespaces         Space (Gb)      Space (Gb)    % Free  AutoExtend  AutoExtend
-------------- --------------- ---------------   ------- ----------- -----------
RBS32                     0.59            0.05      9.10       32.32       31.78
TABLESPACE1               0.99            0.47     48.13       32.32       31.80
SYSTEM                    0.20            0.15     74.57        0.20        0.15
USERS                     0.99            0.99     99.99       32.32       32.32

SQL>