Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

oracle - copy index from one schema to another

SOLVED
Go to solution
Stephen R Badgett
Occasional Visitor

oracle - copy index from one schema to another

<< 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
5 REPLIES
TwoProc
Honored Contributor
Solution

Re: oracle - copy index from one schema to another

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.
We are the people our parents warned us about --Jimmy Buffett
Hein van den Heuvel
Honored Contributor

Re: oracle - copy index from one schema to another

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
Occasional Visitor

Re: oracle - copy index from one schema to another

Recreate them would be the quickest way
Volker Borowski
Honored Contributor

Re: oracle - copy index from one schema to another

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
Occasional Visitor

Re: oracle - copy index from one schema to another

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