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.
|
