Ask the Expert

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

How can I transform the indexes of a table when we are transferring that table from one schema to another schema?

    Requires Free Membership to View

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

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: