Moving a LOB Segment and a LOB Index to a Different Tablespace

Normally we cannot move or rebuild a lob index to another tablespace like normal table.  So we have to use little difference syntax.

SQL> COL COLUMN_NAME FOR A20

SQL> SET LINES 120

SQL> COL OWNER FOR A6

SQL> COL INDEX_TYPE FOR A10

SQL> COL TABLESPACE_NAME FOR A10

SQL>

SQL> select OWNER,TABLESPACE_NAME from dba_tables where table_name='AUD$';

OWNER  TABLESPACE_NAME

------ ------------------------------

SYS    USERS

SQL> select OWNER,TABLESPACE_NAME from dba_tables where table_name='AUD$';

OWNER  TABLESPACE

------ ----------

SYS    USERS

SQL> SELECT OWNER,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='AUD$';

OWNER  COLUMN_NAME          SEGMENT_NAME                   TABLESPACE

------ -------------------- ------------------------------ ----------

SYS    SQLBIND              SYS_LOB0000000384C00040$$      USERS

SYS    SQLTEXT              SYS_LOB0000000384C00041$$      USERS

SQL> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';

OWNER  INDEX_NAME                     INDEX_TYPE                  TABLESPACE

------ ------------------------------ --------------------------- ----------

SYS    SYS_IL0000000384C00041$$       LOB                         USERS

SYS    SYS_IL0000000384C00040$$       LOB                         USERS

SQL> ALTER TABLE AUD$ MOVE TABLESPACE SYSTEM;

Table altered.

Above command will move the table to new tablespace but will not move the
CLOB segment and it will still be in original tablespace. This is because LOB
data is stored outside of the table.

SQL> SELECT OWNER,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='AUD$';

OWNER  COLUMN_NAME          SEGMENT_NAME                   TABLESPACE

------ -------------------- ------------------------------ ----------

SYS    SQLBIND              SYS_LOB0000000384C00040$$      USERS

SYS    SQLTEXT              SYS_LOB0000000384C00041$$      USERS

SQL> ALTER TABLE AUD$ MOVE LOB (SQLBIND) STORE AS (TABLESPACE SYSTEM);

Table altered.

SQL> ALTER TABLE AUD$ MOVE LOB (SQLTEXT) STORE AS (TABLESPACE SYSTEM);

Table altered.

SQL>  select OWNER,TABLESPACE_NAME from dba_tables where table_name='AUD$';

OWNER                                TABLESPACE_NAME

------ --------------------------------------------

SYS                           SYSTEM

SQL> SELECT OWNER,COLUMN_NAME,SEGMENT_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='AUD$';

OWNER     COLUMN_NAME          SEGMENT_NAME                           TABLESPACE

------ -------------------- ------------------------------ -----------------------------------------------

SYS           SQLBIND                       SYS_LOB0000000384C00040$$        SYSTEM

SYS           SQLTEXT                      SYS_LOB0000000384C00041$$          SYSTEM

SQL> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLESPACE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='AUD$';

OWNER  INDEX_NAME                     INDEX_TYPE TABLESPACE

------ ------------------------------ --------------------------------- ----------

SYS    SYS_IL0000000384C00041$$       LOB        SYSTEM

SYS    SYS_IL0000000384C00040$$       LOB        SYSTEM

--Script for genarating sql statements

SQL>SELECT 'ALTER TABLE '||TABLE_NAME||' MOVE TABLESPACE YOUR_TS'||CHR(10)||

                   'LOB ('||COLUMN_NAME||') STORE AS '||SEGMENT_NAME||CHR(10)||

                   '(TABLESPACE YOUR_TS);' SQL_STATEMENTS FROM DBA_LOBS

                   WHERE

                   TABLESPACE_NAME ='SYSTEM' AND OWNER='SYS' AND TABLE_NAME='AUD$';

SQL_STATEMENTS

-------------------------------------------------

ALTER TABLE AUD$ MOVE TABLESPACE YOUR_TS

LOB (SQLBIND) STORE AS SYS_LOB0000000384C00040$$

(TABLESPACE YOUR_TS);

ALTER TABLE AUD$ MOVE TABLESPACE YOUR_TS

LOB (SQLTEXT) STORE AS SYS_LOB0000000384C00041$$

(TABLESPACE YOUR_TS);

[Note: "small" LOBs stored inline (ie in the row itself) are not in a separate LOB SEGMENT at all. That is called STORAGE IN ROW and is the default for LOBs of 4000bytes or less.]