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) Pct_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;
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
1 Comments