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
I was quite surprised to find that there are over 100 tables matching the query, even in a small test database:
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
This query gives the following result:
OWNER TABLE_NAME CLUSTER_NAME PAR T IOT_TYPE
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.