Dba_Data_Files vs Dba_Segments vs Dba_Free_Space


Dba_data_files: The dba_data_files means total size of the data file.

DBA_SEGMENTS: The dba_segments means used size of the data file.

DBA_FREE_SPACE: The dba_free_space means free size of the data file.

DBA_DATA_FILES = DBA_SEGMENTS + DBA_FREE_SPACE + Oracle overhead (header, bitmap… in few KBs)

lets take an example:

SQL> CREATE TABLE sample_table (USERID NUMBER, USERNAME VARCHAR2(20), USERPWD VARCHAR2(10),EMAILID VARCHAR2(20), FIRSTNAME VARCHAR2(10), LASTNAME VARCHAR2(10), ISACTIVE NUMBER) tablespace TR;

Table created.

SQL> INSERT INTO sample_table
2 SELECT srl,
3 name,
4 pwd,
5 LOWER(SUBSTR(name, 1, 10)) || ‘@abc.com’,
6 SUBSTR(name, 1, 10),
7 SUBSTR(name, 11, 20),
8 1
9 FROM (
10 SELECT level srl,
11 dbms_random.string(‘U’, 20) name,
12 dbms_random.string(‘A’, 10) pwd
13 FROM DUAL
14 CONNECT BY LEVEL <= 1000);

1000 rows created.

SQL> commit;

Commit complete.

SQL> Select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_data_files group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                                .09765625

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024) from dba_segments group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                       .00012207

SQL> select tablespace_name,sum(bytes)/(1024*1024*1024)from dba_free_space group by tablespace_name having tablespace_name=’TR’ order by 1;

TABLESPACE_NAME SUM(BYTES)/(1024*1024*1024)
—————————— —————————
TR                                      .096557617

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s