Moving LOB column to a different tablespace

In one of our previous blog post, we had discussed about moving tables to a different tablespace. In this post, we will cover how we can move a LOB column to a different tablespace. Connect to SQL*Plus and create the following table and populate it with some data.

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

INSERT INTO TEST
SELECT rownum,object_name
FROM USER_OBJECTS
/

Whenever we create a table with a CLOB column, Oracle implicitly creates a LOB segment and a LOB index for the CLOB column. By default, LOB segment and index are created in the same tablespace as of the table. During Creation of table, we have an option to specify storage for LOB column. We can also specify whether we want to store it within the table or outside the table. By default storage is outside the table. We can also give specific name to segment and index instead of name being generated by Oracle. If segment name or index name is not defined, normally segments start with SYS_LOBxxxx and indexes start with SYS_ILxxxx.

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

INDEX_NAME INDEX_TYPE STATUS
—————————— ————————— ——–
SYS_IL0000073575C00002$$ LOB VALID

We can also look at user_lob view to see the segment and index.

SQL>SELECT table_name,column_name,segment_name,tablespace_name
FROM user_lobs
WHERE table_name = ‘TEST’
/

TABLE_NAME COLUMN_NAM SEGMENT_NAME TABLESPACE_NAME
———- ———- ————————– —————
TEST TEST_NAME SYS_LOB0000073575C00002$$ USERS

Now let us move table to new tablespace. We are assuming that target tablespace in which you are moving the table already exists in the database.

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