How to move a LOB column to different Tablespace in oracle ?

When we create a table in oracle with CLOB datatype, oracle creates LOB segment and LOB index for this column. By default this LOG segment and LOB index gets created in the tablespace where table is currently residing. This article demonstrate this and also we will discuss how to move a LOB column to different Tablespace in oracle.

SQL> CREATE TABLE TBL_LOB_TEST( OBJECT_ID NUMBER, OBJECT_NAME CLOB);

Table created.

SQL>
SQL> select table_name,tablespace_name from dba_tables where table_name='TBL_LOB_TEST';

TABLE_NAME      TABLESPACE_NAME
--------------  ---------------------
TBL_LOB_TEST    TEST_TBLSPC

Let’s check in which tablespace LOB segments are currently residing.

SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='TBL_LOB_TEST';

TABLE_NAME    SEGMENT_NAME               INDEX_NAME               TABLESPACE_NAME
------------- -------------------------  ------------------------ -----------------
TBL_LOB_TEST  SYS_LOB0000092478C00002$$  SYS_IL0000092478C00002$$ TEST_TBLSPC

If we query these LOB segments with DBA_SEGMENTS, then we will get the same tablespace name.

Now we will move this table to the USERS tablespace.

SQL> ALTER TABLE TBL_LOB_TEST MOVE TABLESPACE USERS;

Table altered.

Now we will again check the tablespace for this table and LOB segments.

SQL>
SQL> SELECT TABLE_NAME,TABLESPACE_NAME FROM DBA_TABLES WHERE TABLE_NAME='TBL_LOB_TEST';

TABLE_NAME     TABLESPACE_NAME
-------------  ---------------- 
TBL_LOB_TEST   USERS

SQL>
SQL>
SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME='TBL_LOB_TEST';

TABLE_NAME SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
TBL_LOB_TEST SYS_LOB0000092478C00002$$ SYS_IL0000092478C00002$$ TEST_TBLSPC

SQL>

From the output of above two query, we can see that the table TBL_LOB_TEST is currently residing in USERS tablespace but its LOB’s are still present in TEST_TBLSPC. In order to move the CLOB column to the USERS tablesace, we need to execute the below command.

SQL> ALTER TABLE TBL_LOB_TEST MOVE LOB(OBJECT_NAME) STORE AS (TABLESPACE USERS);

Table altered.

SQL>

In the above command OBJECT_NAME is the column name of CLOB datatype from the table TBL_LOB_TEST. Again we execute the above query to check the current tablesace for these LOB segments.

SQL> SELECT TABLE_NAME,SEGMENT_NAME,INDEX_NAME,TABLESPACE_NAME FROM DBA_LOBS WHERE TABLE_NAME=’TBL_LOB_TEST’;

TABLE_NAME SEGMENT_NAME INDEX_NAME TABLESPACE_NAME
—————————— —————————— —————————— ——————————
TBL_LOB_TEST SYS_LOB0000092478C00002$$ SYS_IL0000092478C00002$$ USERS

SQL>

Now we can see that the LOB segments are moved to the USERS tablesace.

How to move a LOB column to different Tablespace in oracle ?

Leave a Reply