Q

How to recreate an Oracle index in a new schema with the CREATE command

Learn how to recreate an Oracle index in a new schema using the CREATE command with this sample SQL code from an Oracle expert.

How can I transform the indexes of a table when we are transferring that table from one schema to another schema?
You can use the DBMS_METADATA.GET_DDL to generate the CREATE command to recreate the Oracle index in the other schema. Spool the output to a file, and you have all you need to recreate the index. Something similar to the following is a start:

 

 SPOOL create_my_index.sql SELECT DBMS_METADATA.GET_DDL('INDEX','INDEX_NAME','INDEX_OWNER') FROM dual; SPOOL OFF

Obviously, you'll have to supply the appropriate index name and owner above. Once you have the above done, open the generated SQL file in a text editor and change the owner name in the CREATE command. You can change other options at this time, such as the TABLESPACE name, if you choose.

 

This was first published in June 2009

Dig deeper on Oracle database design and architecture

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close