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 last published in March 2003

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close