Wednesday, March 26, 2014

Oracle DBA_TABLESPACE_USAGE_METRICS vs DBA_DATA_FILE with autoextent tablespaces

If you compare tablespace sizes with following two queries:

SQL> select * from dba_tablespace_usage_metrics

and

SQL> select x.tablespace_name,
    sum(x.bytes/1024/1024) "Total Space (Mb)" ,
    NVL((sum(x.bytes/1024/1024)-round(y.free,2)), sum(x.bytes/1024/1024)) "Used (Mb)",
     NVL(round(y.free,2), 0.00) "Free (Mb)",
     NVL(round(y.free/sum(x.bytes/1024/1024)*100,2), 0) "Free %"
from dba_data_files x,
    (select tablespace_name,sum(bytes/1024/1024) free
         from dba_free_space
         group by tablespace_name) y
where x.tablespace_name = y.tablespace_name (+)
group by x.tablespace_name,y.free;



They are looking very different especially if the tablespaces are using autoextent.
This is because :
Tablespace_size in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for autoextensible tablespace which corresponds to maxblocks in dba_data_files.
So basically DBA_TABLESPACE_USAGE_METRICS shows the maximum size to which a datafile can grow. And DBA_DATA_FILES (+ DBA_FREE_SPACE) current size of tablespace.
 


Look more from MOS (My Oracle Support) document:

No comments:

Post a Comment