I am a simple Senior Oracle DBA Consultant....

Oracle Certified (Database 9i/10g/11g
& Application 11i), dynamic, self-motivated and results-oriented
professional with over 16 years of broad- based experience in Database
Management, Administration, Backup & Recovery, Tuning and Oracle
Application Concepts and Architecture using Oracle Technologies (Database,
Oracle Application and Middleware). Expertise in high-technology time and cost
based solutions, quality customer service and commitment to excellence.

Monday, June 24, 2013

How to move a index from one tablespace to another

How to move index from tablespace to another tablespace online.

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
SYS                                  TEST_SC                               OLD_INDEX


I want to move TEST_SC which is wrongly created in OLD_INDEX tablespace.  User want to move to NEW_INDEX tablespace,

SQL>alter index <INDEX_NAME> rebuild tablespace <NEW TABLESPACE> PARALLEL NOLOGGING;

SQL>alter index TEST_SC rebuild tablespace NEW_INDEX PARALLEL;

SQL>

OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ---------------
SYS                                  TEST_SC                               NEW_INDEX

User carefully with NOLOGGING option if you have DR. 

If you are moving large index, check in V$SESSION_LONGOPS to see how much it progressed and how long it will take (approx).



No comments:

Post a Comment