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

Last updated on JUNE 19, 2017

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

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 hundreds of Community platforms