Skip to Content

Perhaps some of you know the SAP note #825653 Oracle: Common errors in which Martin Frauendorfer collected many “myths” and all-time misconceptions about Oracle databases.
Here’s another one.
“For all tables in an Oracle database, there is a segment in DBA_SEGMENTS.”
This is true for the vast majority of tables in an SAP installation, but not for all.
Let’s take a look at my test instance:
I want to get all tables that DON’T appear in DBA_SEGMENTS:

select owner, table_name from dba_tables 
    where (owner, table_name) not in
                    (select owner, segment_name from dba_segments
                     where segment_type='TABLE');

I was quite surprised to find that there are over 100 tables matching the query, even in a small test database:

OWNER     TABLE_NAME
--------- ------------------------------
SYS       CDEF$
SYS       CCOL$
SYS       FET$
SYS       TS$
SYS       SEG$
SYS       UET$
SYS       TSQ$
SYS       USER$
[...]
SYS       WRH$_SERVICE_STAT
SYS       WRH$_TABLESPACE_STAT
SYS       WRH$_ACTIVE_SESSION_HISTORY
SYS       WRH$_INST_CACHE_TRANSFER
SYS       WRH$_DLM_MISC
[...]
SYSTEM    LOGMNR_COLTYPE$
SYSTEM    LOGMNR_TYPE$
SYSTEM    LOGMNR_TABCOMPART$
SYSTEM    LOGMNR_TABSUBPART$
SYSTEM    LOGMNR_TABPART$
SYSTEM    LOGMNR_IND$
SYSTEM    LOGMNR_TS$
SYSTEM    LOGMNR_ATTRCOL$
[...]
SYS       AQ$_SCHEDULER$_EVENT_QTAB_G
SYS       AQ$_SCHEDULER$_EVENT_QTAB_H
SYS       AQ$_SCHEDULER$_EVENT_QTAB_T
SYS       AQ$_SCHEDULER$_JOBQTAB_I
SYS       AQ$_SCHEDULER$_JOBQTAB_G
SYS       AQ$_SCHEDULER$_JOBQTAB_H
SYS       AQ$_SCHEDULER$_JOBQTAB_T
SYS       RULE_SET_ROP$
[...]
SYS       CLUSTER_INSTANCES
SYS       CLUSTER_NODES
SYS       CLUSTER_DATABASES
SYS       MAP_OBJECT
SYS       ATEMPTAB$
173 rows selected.

Obviously I left most of the tables out, for clarity reasons here.

So, what might be ‘wrong’ with those tables?
Are these tables all *special* Oracle dictionary objects for which the normal rules don’t apply?
Not really.
In fact, displaying a bit more information from the DBA_TABLES view will immediately disclose, what is going on here:

select owner, table_name, cluster_name, partitioned, temporary, iot_type 
    from dba_tables
    where (owner, table_name) not in
            (select owner, segment_name from dba_segments
            where segment_type='TABLE')

This query gives the following result:

OWNER        TABLE_NAME                     CLUSTER_NAME    PAR T IOT_TYPE
------------ ------------------------------ ------------------- - ------------
SYS          CDEF$                          C_COBJ#         NO  N
SYS          CCOL$                          C_COBJ#         NO  N
SYS          FET$                           C_TS#           NO  N
SYS          TS$                            C_TS#           NO  N
SYS          SEG$                           C_FILE#_BLOCK#  NO  N
SYS          UET$                           C_FILE#_BLOCK#  NO  N
SYS          TSQ$                           C_USER#         NO  N
SYS          USER$                          C_USER#         NO  N
[...]
SYS          WRH$_SERVICE_STAT                              YES N
SYS          WRH$_TABLESPACE_STAT                           YES N
SYS          WRH$_ACTIVE_SESSION_HISTORY                    YES N
SYS          WRH$_INST_CACHE_TRANSFER                       YES N
SYS          WRH$_DLM_MISC                                  YES N
[...]
SYSTEM       LOGMNR_COLTYPE$                                YES N
SYSTEM       LOGMNR_TYPE$                                   YES N
SYSTEM       LOGMNR_TABCOMPART$                             YES N
SYSTEM       LOGMNR_TABSUBPART$                             YES N
SYSTEM       LOGMNR_TABPART$                                YES N
SYSTEM       LOGMNR_IND$                                    YES N
SYSTEM       LOGMNR_TS$                                     YES N
SYSTEM       LOGMNR_ATTRCOL$                                YES N
[...]
SYS          AQ$_SCHEDULER$_EVENT_QTAB_G                    NO  N IOT
SYS          AQ$_SCHEDULER$_EVENT_QTAB_H                    NO  N IOT
SYS          AQ$_SCHEDULER$_EVENT_QTAB_T                    NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_I                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_G                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_H                       NO  N IOT
SYS          AQ$_SCHEDULER$_JOBQTAB_T                       NO  N IOT
SYS          RULE_SET_ROP$                                  NO  N IOT
[...]
SYS          CLUSTER_INSTANCES                              NO  Y
SYS          CLUSTER_NODES                                  NO  Y
SYS          CLUSTER_DATABASES                              NO  Y
SYS          MAP_OBJECT                                     NO  Y
SYS          ATEMPTAB$                                      NO  Y
173 rows selected.

As we see, every table that does not occur in DBA_SEGMENTS uses some special storage feature in use.

Tables where column CLUSTER_NAME is not null are part of a cluster – so the cluster itself as a segment.

For those tables that are partitioned only the table partitions will have segments – the table itself is only there as an access entity.

Oracle supports the use of SQL temporary tables – which are stored only as temporary segments. So no entry in DBA_SEGMENTS as long as no session is filling it with data.

And finally there are IOT (index organized tables) that DO get a directly related segment in the DBA_SEGMENT but it is an index segement named SYS_IOT_TOP_<TABLE OBJECT ID>.

Of course there are even more ways to create tables that don’t appear in DBA_SEGMENTS (e.g. external tables), but I just wanted to demonstrate that it is important to keep in mind the subtle difference between a TABLE as a entity you can access via SQL and a SEGMENT which is a Oracle specific storage representation of database objects.
From a database user perspective, you never work with segments but only with TABLES, VIEWS and other SQL objects.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply