I want to find the tablespace of an index organized table with primary key.
dba_segments I can only find the tablespace for the primary key.
But how can I find the tablespace for the table?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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).
dba_tables, you'll see one entry for your table with an
IOT (and pretty much everything else null or defaulted), and, if you have an overflow segment, another entry with
IOT_NAME the name of your table. (The table name for the overflow segment is system-generated, something like