The Oracle (tm) Users' Co-Operative FAQ

Is there any reason why the tablespace_name in user_tables and all_tables is blank for an Index Organized Table?


Author's name: Amarjyoti Dewri

Author's Email: adewri@rediffmail.com

Date written: June 24, 2003

Oracle version(s): Oracle 8i and above

An Index Organized Table was created and i cannot see the tablespace name from the user_tables. Why ?

Back to index of questions


The IOT name itself is a logical name and occupies no space. When an IOT is created it generates two segments.

1. An index name SYS_IOT_TOP_<IOT_object_id>.

2. Optionally an overflow table named SYS_IOT_OVER_<IOT_object_id>.

It is these two segments which occupy space and against which tablespace is visible. To view the tablespace_name first find the object_id of the table and then search against that object_id in all_indexes. Example:

-- 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

Or simply the all_indexes table can be queried directly for the tablespace name.


Further reading: None


Back to top

Back to index of questions