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:

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:

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.