Friday, 20 March 2015

Oracle MetaQuery To Check DB Size and Table Size

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

No comments:

Post a Comment