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?
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_<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 an entity you can access via SQL and a SEGMENT which is an 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.