Moving Tables with LOB columns to a diff. tablespace

In the previous post I discussed how to move table and its indexes to a different tablespace, and I also mentioned that moving tables with LOB columns to a different tablespace is performed by the different way. So here it is how to do this.

First of all, let’s create a test table with LOB column.

CREATE TABLE TST_TABLE(
COL_ID          NUMBER CONSTRAINT TEST_PK PRIMARY KEY,
COL_CLOB   CLOB
);

–Insert one row into this table

INSERT INTO TST_TABLE
VALUES(1,’FIRST ROW’);
COMMIT;

–Query it

SELECT * FROM TST_TABLE

–Result

COL_ID   | COL_CLOB
——————————-
1              | <CLOB>

Now let’s check table’s current tablespace.

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————
TST_TABLE     | SYSTEM

 

Assume that we have already created a test tablespace, let’s say it is called TST_TBS.

–Move table to TST_TBS tablespace

ALTER TABLE TST_TABLE MOVE TABLESPACE TST_TBS

–Let’s check the result

SELECT TABLE_NAME
,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TST_TABLE’

–Result

TABLE_NAME  | TABLESPACE_NAME
————————————————–
TST_TABLE     | TST_TBS

When you create a table with LOB column, oracle automatically creates LOB segment and LOB index. If you do not indicate their(LOB segment and LOB index) names and where they  should be kept, oracle names them itself and creates in the same tablespace as the table is.

By default, segment name starts with SYS_LOB… and index name starts with SYS_IL…

Let’s check what indexes are already created for our table.

SELECT INDEX_NAME                 AS INDX_NM
,INDEX_TYPE                 AS  INDX_TP
,TABLESPACE_NAME   AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                      | INDX_TP | TBS_NM | STATUS
—————————————————————————————–
SYS_IL0000055912C00002$$| LOB         | SYSTEM  | VALID
TEST_PK                                      | NORMAL | SYSTEM  | UNUSABLE

Or you can query the following to identify segment name and index name.

SELECT COLUMN_NAME           AS CLN_NM
,SEGMENT_NAME        AS SEGMENT
,TABLESPACE_NAME  AS TBS_NM
,INDEX_NAME                AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         | TBS_NM | INDX_NM
———————————————————————————————————————-
COL_CLOB|SYS_LOB0000055912C00002$$| SYSTEM |SYS_IL0000055912C00002$$

Because of, by default LOB column is stored outside of the table it is still located in the SYSTEM tablespace(was not moved).

In order to move LOB column to a different tablespace, you should run the following command.

ALTER TABLE TST_TABLE MOVE LOB(COL_CLOB)
STORE AS (TABLESPACE TST_TBS);

–Check it

SELECT COLUMN_NAME         AS CLN_NM
,SEGMENT_NAME       AS SEGMENT
,TABLESPACE_NAME AS TBS_NM
,INDEX_NAME               AS INDX_NM
FROM USER_LOBS
WHERE TABLE_NAME=’TST_TABLE’

–Result

CLN_NM     |SEGMENT                                         |TBS_NM  | INDX_NM
———————————————————————————————————————–
COL_CLOB|SYS_LOB0000055912C00002$$|TST_TBS |SYS_IL0000055912C00002$$

Let’s query the following:

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | SYSTEM  | UNUSABLE

As you can see, LOB index was automatically moved to the new tablespace, but normal index still is in the same tablespace. To move it to a different tablespace, run the following:

ALTER INDEX TEST_PK REBUILD TABLESPACE TST_TBS

–Check it

SELECT INDEX_NAME               AS INDX_NM
,INDEX_TYPE                AS INDX_TP
,TABLESPACE_NAME AS TBS_NM
,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TST_TABLE’

–Result

INDX_NM                                       | INDX_TP | TBS_NM | STATUS
——————————————————————————————
SYS_IL0000055912C00002$$ | LOB         | TST_TBS |  VALID
TEST_PK                                       | NORMAL | TST_TBS |  VALID

 

After rebuilding an index, it became valid. As I also said in the previous post , if at least one index of the table is not valid DML operations will fail.