I want to find the tablespace of an index organized table with primary key.

With dba_segments I can only find the tablespace for the primary key.

But how can I find the tablespace for the table?


Index organized tables (IOTs) don't actually have a normal heap table-type segment as their main storage - that's pretty much the point of IOTs. The data is stored in a B-Tree structure, essentially like a normal Oracle index.

So you won't see any segments in dba_segments for the table itself. What you will see are (index) segments for the primary key, and possibly overflow segments (this is used when you want to store parts of the row outside the B-Tree structure).

In dba_tables, you'll see one entry for your table with an IOT_TYPE of IOT (and pretty much everything else null or defaulted), and, if you have an overflow segment, another entry with IOT_TYPE of IOT_OVERFLOW and IOT_NAME the name of your table. (The table name for the overflow segment is system-generated, something like SYS_IOT_OVER_XYZ.)

See also:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.