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 segment named SYS_IOT_TOP_