0
To check the space available in a given table_space
With total allocation
select a.tablespace_name,
a.physical_bytes/(1024*1024) physical_mbytes,
a.bytes_alloc/(1024*1024) mbytes_alloc,
(a.bytes_alloc-nvl(b.tot_used,0))/(1024*1024) tot_free,
nvl(b.tot_used,0)/(1024*1024) tot_used,
round((nvl(b.tot_used,0)/a.bytes_alloc)*100,2) pct_used,
decode(a.tablespace_name,'TEMP',-1,sign(((nvl(b.tot_used,0)/a.bytes_alloc)*100)-90)) full
from ( select tablespace_name,
sum(bytes) physical_bytes,
sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
from dba_data_files
group by tablespace_name ) a,
( select tablespace_name, sum(bytes) tot_used
from dba_segments
group by tablespace_name ) b
where a.tablespace_name = b.tablespace_name (+)
order by 6 desc, 5 desc, 1
To get table size in a schema
select segment_name,
sum(bytes)/(1024*1024) MBytes from dba_segments
where owner = 'OBA_DW' --and segment_name like '%S'
group by segment_name
order by sum(bytes)/(1024*1024) desc
0Awesome Comments!