Tablespace of an index organized table

Question

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?

Answer 1

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:

• Using Index-Organized Tables for Performance
• Managing Index-Organized Tables