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

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

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