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 ?