oracle - copy index from one schema to another

[Stephen R Badgett]

<< Oracle Standard version only >>

this is what I have done so far ...

create table <<>>.DEPT as select * from <<>>.DEPT;

I need know how to copy the indexes over from my old schema to the new one (in a script)

thank you

Steve

[TwoProc · Accepted solution]

Since the amount of disk/effort required is the same, just drop then recreate the index where it needs to go. A "copy" of an index would be literally the same thing.

[Hein van den Heuvel]

As John indicates, copying an index makes no sense. you just re-create it using the same definition.

Idealy you simply have the defintion script handy.

You could have copied data + index using an export + import.

You can still use export with parameters ROWS=N, INDEXES=Y, to just get index definitions to a file.

I believe the official way it to use GET_DDL from the metadata package.

Something like

select DBMS_METADATA.GET_DDL('INDEX','DEPT_INDEX_1') from DUAL;
:
CREATE INDEX "schema".DEPT_INDEX_1" ON "schema"."DEPT" ...

One you have the text... execute it!

Hope this helps some,
Hein van den Heuvel (at gmail dot com)
HvdH Performance Consulting

[Stephen R Badgett]

Recreate them would be the quickest way

[Volker Borowski]

Steve,

no a RE-Create (or rebuild) will do this for the index on the old table.

You need to create a new index that indexes the new table!

It is psooible to create an index in the new schema, that indexes the old table, so use Heins approach an change the schema-user for the index AND the table to do the copy!

Volker

[Stephen R Badgett]

I did realize that and a sed on the file did the trick quickly -- thank for all the help