Ask the Expert

Moving indexes from one tablespace to another

Can you advise me on the command to move indexes from one tablespace to another?

    Requires Free Membership to View

Here's an example:

SQL> desc students
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------
 ID                                        NOT NULL NUMBER(5)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                          VARCHAR2(20)
 MAJOR                                              VARCHAR2(30)
 CURRENT_CREDITS                                    NUMBER(3)
 
SQL> create index test_stud_idx on students (major) ;
Index created.

SQL> select tablespace_name from user_indexes where index_name = 'TEST_STUD_IDX' ;

TABLESPACE_NAME
------------------------------
USERS

SQL> alter index test_stud_idx rebuild tablespace indx online ;
Index altered.

SQL> select tablespace_name from user_indexes where index_name = 'TEST_STUD_IDX';

TABLESPACE_NAME
------------------------------
INDX
If you have a Metalink ID, you can also find a very detailed procedural way to do this on Metalink.

For More Information


This was first published in March 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: