Author's Email: adewri@rediffmail.com

Oracle version(s): Oracle 8i and above

-- Lets First Create a sample INDEX ORGANIZED TABLE named DELME_IOT.

CREATE TABLE DELME_IOT
(
A INTEGER,
B INTEGER NOT NULL,
C INTEGER NOT NULL,
D INTEGER,
PRIMARY KEY (C, B)
)
ORGANIZATION INDEX
LOGGING
TABLESPACE SYSTEM
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;

--Now we will query the tablespace name against this table.

SQL> select table_name,tablespace_name from all_tables
2 where table_name like 'DELME_IOT';

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
DELME_IOT

--Here we cannot see the tablespace name. Lets first find the object_id for this table.

SQL> select object_id, object_name from all_objects where object_name like 'DELME_IOT';

OBJECT_ID OBJECT_NAME
---------- ------------------------------
56709 DELME_IOT

-- Here we have the object_id for the table DELME_IOT. Next we will query the all_indexes table.

SQL> select table_name,index_name,tablespace_name from all_indexes where index_name like '%56709%';

TABLE_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
DELME_IOT SYS_IOT_TOP_56709 SYSTEM

--Here we can see that tablespace name against the IOT