Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Difference between output of DBA_SEGMENTS,DBA_OBJECTS & DBA_TABLES.

Hemant InfyJan 21 2014 — edited Jan 22 2014

Hi All,

I am a newbie to Oracle, was checking  total no. of tables present in the DB, but got little confused now.

I tried querying DBA_SEGMENTS, DBA_OBJECTS and DBA_TABLES but got different outputs.

SQL> select object_type,count(1) from dba_objects where OBJECT_TYPE='TABLE' group by object_type;

OBJECT_TYPE                     COUNT(*)
-------------------           ----------
TABLE                               2437

SQL> select segment_type,count(1) from dba_segments where segment_type='TABLE' group by segment_type;

SEGMENT_TYPE                   COUNT(*)
------------------           ----------
TABLE                         1631

If a table is a partitioned one, then each partition will be considered as a different segment hence I checked for table partition also.

SQL> select segment_type,count(*) from dba_segments where segment_type like 'TABLE_%' group by segment_type;

SEGMENT_TYPE         COUNT(*)
------------------ ----------
TABLE SUBPARTITION         32
TABLE PARTITION           215

still the addition of above two does not give (32+215+1631=1878), i.e., not 2437

Then tried querying DBA_TABLES:

SQL> select count(1) from dba_tables;

  COUNT(1)
----------
      2417

could you please help me to understand difference between these three and which one is the correct answer ?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 19 2014
Added on Jan 21 2014
7 comments
2,563 views