Moving table(s) to a different tablespace in Oracle

In one of our previous blog post, we covered how to move a table to a different file group in SQL Server 2005. In this blog post, we will see how we can move a table to a different tablespace in Oracle.

There are a couple of ways in which a table can be moved to a different tablespace:

1) One of them is to perform export/import.
2) Another approach is to use the ‘alter table’ command with ‘move tablespace’ clause.

Let us start by creating a table. Connect to SQL*Plus using proper credentials and run the following SQLs:

CREATE TABLE TEST
(
TEST_ID NUMBER(9) NOT NULL,
TEST_DESC VARCHAR(10),
CONSTRAINT PK_TEST PRIMARY KEY(TEST_ID)
)
/

CREATE INDEX TEST_IND_1 ON TEST(TEST_DESC)
/

Above SQLs creates table and index in user’s default tablespace. In our case it is ‘users’ tablespace. Run following SQL to check where these objects are created.

SELECT TABLE_NAME,TABLESPACE_NAME,STATUS
FROM User_Tables
WHERE TABLE_NAME = ‘TEST’
UNION ALL
SELECT INDEX_NAME,TABLESPACE_NAME,STATUS
FROM User_Indexes
WHERE TABLE_NAME = ‘TEST’
/

And the result is

TABLE_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
TEST USERS VALID
TEST_IND_1 USERS VALID
PK_TEST USERS VALID

Since we are using UNION ALL, two indexes PK_TEST (Primary key index) and TEST_IND_1 are also shown under TABLE_NAME column. Result shows that table and its corresponding indexes are crated in USERS tablespace and in valid status. Now let us insert one record into the table.

INSERT INTO TEST VALUES(1,’Hello’);
COMMIT;

Now let us retrieve value for TEST_ID column along with value of pseudo column ROWID. We will explain you later in the article, why we are retrieving ROWID. Following is the SQL followed by result.

SQL> SELECT ROWID, TEST_ID FROM TEST;

ROWID TEST_ID
—————— ———-
AAAQ+eAAEAAAA3tAAA 1

Now we will move the TEST table to another tablespace ‘SLMDATA’. We already have another tablespace SLMDATA in place. If you don’t have another tablespace where you can move the table, please create the tablespace first for testing purpose. Following command will move table TEST to new tablespace SLMDATA.

SQL> ALTER TABLE TEST MOVE TABLESPACE SLMDATA;

Above command will move table to new tablespace SLMDATA. Now let us check ROWID value again by re-issuing previous SQL.

SQL> SELECT ROWID, TEST_ID FROM TEST;

ROWID TEST_ID
—————— ———-
AAAQ+hAAHAAAAAsAAA 1

So far we have moved only table. Moving table to new tablespace will make all the indexes on the table unusable. We can verify it by issuing the SQL we executed initially after creation of table and index.

TABLE_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
TEST SLMDATA VALID
TEST_IND_1 USERS UNUSABLE
PK_TEST USERS UNUSABLE

Result shows that table has been moved to new tablespace SLMDATA and is in ‘valid’ status while indexes still point to older tablespace and are in ‘unusable’ status. Indexes in unusable state will prevent any DML activity on the table. We can verify this by inserting new record into TEST table.

INSERT INTO TEST VALUES(2,’World!’);

Running above SQL will result in following error.

ERROR at line 1:
ORA-01502: index ‘DECIPHER.PK_TEST’ or partition of such index is in unusable
State

Why this happened? When we moved table to new tablespace, each row of the table got moved and got new ROWID. If you compare the ROWID values before and after the move, you will realize that ROWID for the same TEST_ID is different. Indexes point to the previous location of the row and not to the current location. This warrants for rebuilding of an index. If you also want to move index to the new tablespace then you can include tablespace clause. If you want to keep the index in the same tablespace where it is currently, you just need to issue only rebuild clause. Following is the example of both.

ALTER INDEX PK_TEST REBUILD;
ALTER INDEX TEST_IND_1 REBUILD TABLESPACE SLMDATA;

First statement only rebuilds the primary key index without moving it to new tablespace. Second statement rebuilds the index and also moves it to the other tablespace. Verify it by issuing following SQL.

SELECT INDEX_NAME,TABLESPACE_NAME,STATUS
FROM User_Indexes
WHERE TABLE_NAME = ‘TEST’;

Result is

INDEX_NAME TABLESPACE_NAME STATUS
—————————— —————————— ——–
TEST_IND_1 SLMDATA VALID
PK_TEST USERS VALID

Once indexes are rebuilt, all DML operations can be resumed on the table. If you try to insert the record, which gave error previously, should work fine after rebuilding all the indexes on the table. One thing to remember is that ‘MOVE TABLESPACE’ does not work if table contains column with LONG or LONG RAW data type. You will run into ‘ORA-00997: illegal use of LONG datatype’ error. Such tables can be moved to new tablespace using exp/imp command.