How to move the table from one tablespace to another in Oracle database 11gR2
Posted by Mir Sayeed Hassan on January 22nd, 2018
How to move the table from one tablespace to another in Oracle database 11gR2
Fallow the step by step procedure as shown below for testing env:
sys@testdb> sho user USER is "SYS"
sys@testdb> create user mir identified by mir; User created.
sys@testdb> grant connect,resource to mir; Grant succeeded.
sys@testdb> select username,default_tablespace from dba_users where username='MIR'; USERNAME DEFAULT_TABLESPACE ------------------------------ ----------------------------- MIR USERS
mir@testdb>create table test1(eno number(10)); Table created. mir@testdb> insert into test1 values(10); 1 row created. mir@testdb> / 1 row created. mir@testdb> commit; Commit complete.
mir@testdb> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ----------------- TEST1 USERS
mir@testdb> create table test2(eno number(10)); Table created. mir@testdb> insert into test2 values(10); 1 row created. mir@testdb> commit; Commit complete.
mir@testdb> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ----------------- TEST1 USERS TEST2 USERS
sys@testdb> select username,default_tablespace from dba_users where username='MIR'; USERNAME DEFAULT_TABLESPACE ------------------------------ --------------------- MIR USERS
index_name : TEST1_INDEX1
mir@testdb> select index_name ,status from user_indexes where table_name='TEST1'; INDEX_NAME STATUS ------------------------------ -------- TEST1_INDEX1 VALID
==================================================================
Now perform some table move from USERS to MIR Tablespace
sys@testdb> alter user mir default tablespace mir; User altered.
sys@testdb> select username,default_tablespace from dba_users where username='MIR'; USERNAME DEFAULT_TABLESPACE ------------------------------ -------------------- MIR MIR
Syntax:
ALTER TABLE
MOVE TABLESPACE
sys@testdb> ALTER TABLE mir.test1 MOVE TABLESPACE mir; Table altered.
mir@testdb> select index_name ,status from user_indexes where table_name='TEST1'; INDEX_NAME STATUS ------------------------------ ------------------- TEST1_INDEX1 UNUSABLE
Note: After you move the table – all the index will be in unusable state, Need to rebuild those index online
Reason to rebuild the index is the rowids of each and every row in the table have changed, so that is why you need to rebuild the indexes
mir@testdb> alter index test1_index1 rebuild online; Index altered.
mir@testdb> select index_name ,status from user_indexes where table_name='TEST1'; INDEX_NAME STATUS ------------------------------ ------- TEST1_INDEX1 VALID
mir@testdb> select table_name,tablespace_name from user_tables; TABLE_NAME TABLESPACE_NAME ------------------------------ ----------------------------------- TEST2 USERS TEST1 MIR --------- The table test1 is moved from USERS Tbs to MIR Tbs