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:
|
I was quite surprised to find that there are over 100 tables matching the query, even in a small test database:
|
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:
|
This query gives the following result:
|
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
36 | |
25 | |
17 | |
13 | |
8 | |
7 | |
7 | |
6 | |
6 | |
6 |