Script – Tablespace Information

Script 1: Tablespace information

SET linesize 150
column tablespace_name format a20 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total| Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(MB)'
column Pct_Free format 999.99 heading '% Free'
column Chunks_Free format 9999 heading 'No Of Ext.'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
SET echo OFF
PROMPT FREE SPACE AVAILABLE IN TABLESPACES
SELECT   a.tablespace_name           ,
         SUM(a.tots   /1048576)        Tot_Size,
         SUM(a.sumb   /1048576)        Tot_Free,
         SUM(a.sumb)  *100/SUM(a.tots) Percent_Free,
         SUM(a.largest/1024)           Max_Free,
         SUM(a.chunks)                 Chunks_Free
FROM     ( SELECT  tablespace_name,
                  0          tots          ,
                  SUM(bytes) sumb          ,
                  MAX(bytes) largest       ,
                  COUNT(*)   chunks
         FROM     dba_free_space a
         GROUP BY tablespace_name
         
         UNION
         
         SELECT   tablespace_name,
                  SUM(bytes) tots,
                  0,0,0
         FROM     dba_data_files
         GROUP BY tablespace_name
         )
         a
GROUP BY a.tablespace_name
ORDER BY pct_free;

Output like:

                         Total
Tablespace            Size(Mb) Total Free(MB)  % Free Max Free(Kb) No Of Ext.
-------------------- --------- -------------- ------- ------------ ----------
SYSTEM                     790              3     .38        3,008          2
SYSAUX                     752             52    6.86       32,768        132
USERS                        5              1   11.25          576          1
MGMT_ECM_DEPOT_TS          100             43   43.25       43,968          2
MGMT_TABLESPACE         13,940          8,388   60.17      155,200       1594
UNDOTBS1                   605            491   81.07      311,360         44
PATROL                       1              1   93.75          960          1

Script 2: Tablespaces With Less Than 10% Free Space

set pagesize 300
set linesize 100
column tablespace_name format a15 heading 'Tablespace'
column sumb format 999,999,999
column extents format 9999
column bytes format 999,999,999,999
column largest format 999,999,999,999
column Tot_Size format 999,999 Heading 'Total Size(Mb)'
column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
column Pct_Free format 999.99 heading '% Free'
column Max_Free format 999,999,999 heading 'Max Free(Kb)'
column Min_Add format 999,999,999 heading 'Min space add (MB)'

ttitle center 'Tablespaces With Less Than 10% Free Space' skip 2
set echo off

select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
sum(a.sumb/1024) Tot_Free,
sum(a.sumb)*100/sum(a.tots) Pct_Free,
ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
from
(
select tablespace_name,0 tots,sum(bytes) sumb
from dba_free_space a
group by tablespace_name
union
select tablespace_name,sum(bytes) tots,0 from
dba_data_files
group by tablespace_name) a
group by a.tablespace_name
having sum(a.sumb)*100/sum(a.tots) < 10
order by pct_free;