Moving tables and indexes to a different tablespace
March 5, 2011 1 Comment
Moving table to a different tablespace can be done by several ways:
- Using Export/Import.
- 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.
Pingback: Moving Tables with LOB columns to a different tablespace | GEOGEEKS.NET