Wednesday, March 10, 2010

Oracle move table to another Tablespace

If you move a table from one table to another tablespaces, you need to rebuild the Index

1. Checking Index for the tables
SQL> SELECT INDEX_NAME,TABLE_NAME,STATUS
FROM ALL_INDEXES
WHERE TABLE_NAME='IB_ARC_SI_BILL_PAYMENT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C003164 IB_ARC_SI_BILL_PAYMENT VALID

2. Moving to antoher tablespace
SQL> ALTER TABLE IB_ARC_SI_BILL_PAYMENT MOVE TABLESPACE ARCHIVE;

3. Checking Index for the tables again
SQL> SELECT INDEX_NAME,TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME='IB_ARC_SI_BILL_PAYMENT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C003164 IB_ARC_SI_BILL_PAYMENT UNUSABLE

4. Rebuild the Index
SQL> ALTER INDEX SYS_C003164 REBUILD;

Index altered

5. Check the index status
SQL> SELECT INDEX_NAME,TABLE_NAME,STATUS FROM ALL_INDEXES WHERE TABLE_NAME='IB_ARC_SI_BILL_PAYMENT';

INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C003164 IB_ARC_SI_BILL_PAYMENT VALID

Finish rebuilding index

No comments: