About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, March 4, 2013

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

2 comments: