Move/Change tablespace of a table

Lets try to move/change tablespace of a table

·         Execute the following commands as a DBA user:

CREATE USER test1 IDENTIFIED BY password

GRANT connect, resource, create session, create table TO test1;

CREATE TABLESPACE mytbs DATAFILE '/opt/oracle/product/10.2.0/db_1/dbs/mytbs.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

CREATE TABLESPACE newtbs DATAFILE '/opt/oracle/product/10.2.0/db_1/dbs/newtbs.dbf' SIZE 1M AUTOEXTEND ON NEXT 1M;

We will try to move a table created on "mytbs" to "newtbs" including the indexes

·         Now connect to Oracle with the user and create the table:

sqlplus test1/password@ora10g

test1@ORA10G> CREATE TABLE test_table (id number) TABLESPACE mytbs;

test1@ORA10G> select table_name ,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

----------------------               ------------------------------

TEST_TABLE                        MYTBS

·         Now create the index on the table:

test1@ORA10G> create index test_index on test_table (id) tablespace mytbs;

test1@ORA10G> select index_name, table_name, tablespace_name from user_indexes;

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME

------------------------------   ----------------------------          -----------------

TEST_INDEX                        TEST_TABLE                        MYTBS

·         Move the table to new tablespace:

test1@ORA10G> alter table test_table move tablespace newtbs;

test1@ORA10G> select table_name ,tablespace_name from user_tables;

TABLE_NAME                     TABLESPACE_NAME

--------------------                                ------------------------------

TEST_TABLE                           NEWTBS

·         What about the index on that table:

Moving a table changes the rowids of the rows in the table. This will cause the indexes on the table to be unusable, and accessing the table using those indexes will receive an ORA error.

 test1@ORA10G> analyze index TEST_INDEX validate structure;

analyze index TEST_INDEX validate structure

*

ERROR at line 1:

ORA-01502: index 'TEST1.TEST_INDEX' or partition of such index is in unusable state

·         Do this if you want the index to move to different tablespace:

test1@ORA10G> alter index TEST_INDEX rebuild tablespace newtbs;

·        If you want the index to remain in old tablespace just execute(alter index TEST_INDEX rebuild).

·         Now check:

test1@ORA10G> analyze index TEST_INDEX validate structure;

·         Now verify that index is moved:

test1@ORA10G> select index_name, TABLE_NAME, TABLESPACE_NAME from user_indexes;

INDEX_NAME                     TABLE_NAME                     TABLESPACE_NAME

------------------------------    ------------------------------      ------------------

TEST_INDEX                         TEST_TABLE                        NEWTBS

Note: DML operations will be blocked on the table when we do ALTER .. MOVE statements.