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.

Moving tables and indexes to a different tablespace

Moving table to a different tablespace can be done by several ways:

1. Using Export/Import.
2. Or by the following clause:

ALTER TABLE schemaName.tableName REBUILD TABLESPACE tablespaceName;

I will discuss the second variant now.

–Let’s create a test table.

CREATE TABLE TESTTABLE(
TESTCOLUMN NUMBER CONSTRAINT TEST_PK PRIMARY KEY
);

–Insert one row into that table

INSERT INTO TESTTABLE
VALUES(1);
COMMIT;

–Let’s see rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoRAABAAAPVSAAA  | 1

Before you move table to a different tablespace, you must have created  this tablespace before.

–Now let’s create a test tablespace.

CREATE TABLESPACE testTablespace
DATAFILE   ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\testTablespace01.DBF’
SIZE 5242880
AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO

–Let’s see table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | SYSTEM

–Move this table to a test tablespace

ALTER TABLE TESTTABLE MOVE TABLESPACE TESTTABLESPACE;

–See again table’s current tablespace

SELECT TABLE_NAME,TABLESPACE_NAME
FROM DBA_TABLES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

TABLE_NAME  | TABLESPACE_NAME
—————————————————-
TESTTABLE     | TESTTABLESPACE

–Let’s see again rowid value

SELECT ROWID,TESTCOLUMN
FROM TESTTABLE;

–Result

ROWID                                | TESTCOLUMN
——————————————————–
AAANoSAAHAAAAAMAAA | 1

As you can see rowid was changed. It makes indexes of this table unusable. To check it, run the following query.

SELECT INDEX_NAME,TABLE_NAME,STATUS,TABLESPACE_NAME
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’;

–Result

INDEX_NAME | TABLE_NAME | STATUS       | TABLESPACE_NAME
—————————————————————————————–
TEST_PK         | TESTTABLE    | UNUSABLE | SYSTEM

Any DML operation on this table will cause an error, because an index is not valid. Let’s check it:

INSERT INTO TESTTABLE
VALUES(2);
COMMIT;

–Error

ORA-01502: index ‘SYS.TEST_PK’ or partition of such index is in unusable state

This happens because you have moved test table to another tablespace, and this operation changed rowid value. The index still refers to the old rowid value. To solve this, you must rebuild the index.

ALTER INDEX TEST_PK REBUILD TABLESPACE TESTTABLESPACE;

This command will rebuild the index and also moves it to TESTTABLESPACE tablespace. If you want to rebuild index without moving it to another tablespace. Simply, run the following command:

ALTER INDEX TEST_PK REBUILD;

–Let’s check its status

SELECT INDEX_NAME,TABLE_NAME,STATUS
FROM DBA_INDEXES
WHERE TABLE_NAME=’TESTTABLE’

–Result

INDEX_NAME | TABLE_NAME | STATUS  | TABLESPACE_NAME
—————————————————————————————
TEST_PK         | TESTTABLE    | VALID       | TESTTABLESPACE

You must do this action for each index, to make DML operation resumable on this table.

Note that, if table contains LOB columns this variant doesn’t work. Moving tables with LOB columns to another tablespace is discussed here.