My Oracle Support Banner

How To Move An IOT Table To Another Tablespace (Doc ID 453997.1)

Last updated on AUGUST 04, 2018

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.3 to 11.2.0.2 [Release 10.2 to 11.2]
Oracle Database - Standard Edition - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.




Goal

An index-organized table has a storage organization that is a variant of a primary B-tree. Unlike an ordinary (heap-organized) table whose data is stored as an unordered collection (heap), data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Each leaf block in the index structure stores both the key and nonkey columns. This Documents provides the steps to  to move an IOT table to another Tablespace.

List the IOT table then run the select to locate which IOT that you want to move:

SELECT OWNER, INDEX_NAME, INDEX_TYPE, TABLE_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_TYPE LIKE 'IOT%';

eg:

OWNER          INDEX_NAME                            INDEX_TYPE               TABLE_NAME                           TABLESPACE_NAME
------------      --------------------                  ------------------------    ------------------------------     ---------------------
APPLSYS            SYS_IOT_TOP_1610787            IOT - TOP               DR$FND_LOBS_CTX$K               APPLSYSD

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.