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.
Dig Deeper on Oracle database design and architecture
Related Q&A from Brian Peasland
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs. Continue Reading
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command. Continue Reading
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming. Continue Reading