Q

Moving indexes from one tablespace to another

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

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

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close