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