Moving LOB(CLOB or BLOB or LONG) columns to different tablespace

Move tables having column as CLOB datatype :-

SQL> desc table_clob;
 Name              Null?     Type
 -----------------   --------     ------------
 ID                                NUMBER(1)
 NAME                         VARCHAR2(2)
 DATA                          CLOB


SQL> select segment_name,tablespace_name from user_segments where segment_name='TABLE_CLOB';

SEGMENT_NAME                              TABLESPACE_NAME
-------------------------------------------------         ----------------------
TABLE_CLOB                                       AMIT

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                               TABLESPACE_NAME                  STATUS
----------------------------------------                ------------------------------                   --------
SYS_IL0000053543C00003$$          AMIT                                            VALID
TEST_IDX                                     AMIT                                            VALID


SQL>  alter table TABLE_CLOB move tablespace system;

Table altered.


SQL> select segment_name,tablespace_name from user_segments where segment_name='TABLE_CLOB';

SEGMENT_NAME                                          TABLESPACE_NAME
---------------------------------                                    ------------------------------
TABLE_CLOB                                                     SYSTEM

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                                   TABLESPACE_NAME                STATUS
------------------------------ -                    -----------------------------                          --------
SYS_IL0000053543C00003$$             AMIT                                            VALID
TEST_IDX                                         AMIT                                         UNUSABLE

Index associated to the table becomes unusable and column having clob datatype still remains in AMIT tablespace, it has to be moved manually.


SQL> ALTER TABLE TABLE_CLOB MOVE LOB(DATA) STORE AS (TABLESPACE SYSTEM);

Table altered.


SQL>  alter index TEST_IDX rebuild tablespace system;

Index altered.


SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TABLE_CLOB';

INDEX_NAME                                TABLESPACE_NAME                STATUS
------------------------------                         ------------------------------                  --------
TEST_IDX                                       SYSTEM                                      VALID
SYS_IL0000053543C00003$$           SYSTEM                                     VALID

Move tables having column as BLOB datatype :-

SQL> desc test_blob
 Name              Null?    Type
 ----------------- --------   ------------
 ID                                NUMBER(12)
 NAME                        VARCHAR2(22)
 DATA                         BLOB


SQL> select segment_name,tablespace_name from user_segments where segment_name='TEST_BLOB';

SEGMENT_NAME                                        TABLESPACE_NAME
--------------------------------------                             ------------------
TEST_BLOB                                                  USERS


SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                         TABLESPACE_NAME                STATUS
------------------------------                              ------------------------------                          --------
SYS_IL0000053551C00003$$                    USERS                                         VALID
TEST_BLOB_IDX                                    USERS                                         VALID


SQL> alter table TEST_BLOB move tablespace amit;

Table altered.

SQL> select segment_name,tablespace_name from user_segments where segment_name='TEST_BLOB';

SEGMENT_NAME                                            TABLESPACE_NAME
----------------------------------------                              ------------------------------
TEST_BLOB                                                       AMIT

SQL>  SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                     TABLESPACE_NAME                STATUS
------------------------------                           ------------------------------                  --------
SYS_IL0000053551C00003$$                USERS                                      VALID
TEST_BLOB_IDX                                USERS                                         UNUSABLE

Index associated to the table becomes unusable and column having clob datatype still remains in AMIT tablespace, it has to be moved manually.

SQL> alter index TEST_BLOB_IDX rebuild tablespace AMIT;

Index altered.

SQL> ALTER TABLE TEST_BLOB MOVE LOB(DATA) STORE AS (TABLESPACE amit);

Table altered.

SQL> SELECT INDEX_NAME,TABLESPACE_NAME,status  FROM USER_indexes WHERE TABLE_NAME='TEST_BLOB';

INDEX_NAME                                       TABLESPACE_NAME                STATUS

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

TEST_BLOB_IDX                                 AMIT                                            VALID

SYS_IL0000053551C00003$$                AMIT                                             VALID

Move columns having column as LONG datatype :-

Long Datatype becomes depricated datatype in Oracle latest releases as with Long as datatype there are too many restrictions. If you have a column in your table as LONG datatype and you want to move the table to another tablespaces, then through move you cannot do that , you will below mentioned error:-

SQL> alter table TEST_LONG move tablespace users;
alter table TEST_LONG move tablespace users
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

Solution:-

1. Export the table having column as LONG datatype using datapump.
2. drop the table.
3. Import the table again with remap_tablespace parameter using impdp.

I hope this article helped you.

Regards,
Amit Rath