How to move lobsegment and lobindex to a different Tablespace

Hi,

Assuming that I have table "TEST" in USERS TableSpace

CREATE TABLE TEST
(
TEST_ID NUMBER NOT NULL,
TEST_NAME CLOB,
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/


SQL> ALTER TABLE TEST MOVE TABLESPACE EXAMPLE;

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.
Check the tablespace of the CLOB column by issuing following sql.

SQL> SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = ‘TEST’;

INDEX_NAME TABLESPACE_NAME
—————————— —————-
SYS_IL0000073575C00002$$ USERS

In order to move CLOB column to different tablespace, we have to issue following command.

SQL> ALTER TABLE TEST MOVE LOB(TEST_NAME) STORE AS (TABLESPACE EXAMPLE);

In above example, TEST_NAME is the CLOB column which we want to move to new tablespace and EXAMPLE is target tablespace. Above command will successfully move LOB segments to the new tablespace. We can verify it by issuing same sql again.

SQL> SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = ‘TEST’;

INDEX_NAME TABLESPACE_NAME
—————————— —————
SYS_IL0000073575C00002$$ EXAMPLE

- Pavan Kumar N