August 20, 2008 9 minute read

Oracle – Popular Misconceptions: Tables without Segments

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.

Be the first to leave a comment

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

Oracle – Popular Misconceptions: Tables without Segments

Assigned Tags