Wednesday, December 21, 2011

dba_data_files , dba_segments and dba_free_space

Dba_data_files:
The dba_data_files means total size of the data file. The data file size is total number of the dba_free_space + dba_segments.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name order by 1;

This example display the total size of the data file:
SQL> select sum(bytes)/(1024*1024*1024) from dba_data_files;
SUM(BYTES)/(1024*1024*1024)
---------------------------
6817.83142


Dba_segments:

The dba_segments means used size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name order by 1;
This example display the total size of the segments:
SQL> select sum(bytes)/(1024*1024*1024) from dba_segments;
SUM(BYTES)/(1024*1024*1024)
---------------------------
5273.55779

Dba_free_space:
The dba_free_space means free size of the data file.
SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name order by 1;

This example display the total size of the free space:
SQL> select sum(bytes)/(1024*1024*1024) from dba_free_space;
SUM(BYTES)/(1024*1024*1024)
---------------------------
1516.29922

Instead of using Dba_data_files , Dba_segments and Dba_free_space you can also use sm$ts_avail , sm$ts_used , sm$ts_free respectively.
As I mentioned earlier sum of dba_free_space and dba_segments should meet dba_data_files . But from our observation it can not meet our requirement (1516.29922+5273.55779=6789.85 only )

As I googled why it Happen, I got some thing like this.
First 8 blocks of each datafiles were not used ( may be some header information is stored ) Because their entries were neither present in the dba_extents nor in dba_free_spaces..
which lead to the difference of few KBs
[dba_data_file - ( dba_segments + dba_free_space ) ] -> few KBs
But for production database the difference is in GBs ..so the above conclusion is not the only way which is leading to the difference.

No comments:

Post a Comment