topic Re: oracle - copy index from one schema to another in Operating System - HP-UX

oracle - copy index from one schema to another

Stephen R Badgett — Fri, 07 Sep 2007 12:23:37 GMT

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

Re: oracle - copy index from one schema to another

TwoProc — Fri, 07 Sep 2007 14:56:08 GMT

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.

Re: oracle - copy index from one schema to another

Hein van den Heuvel — Fri, 07 Sep 2007 15:16:12 GMT

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

Re: oracle - copy index from one schema to another

Stephen R Badgett — Fri, 07 Sep 2007 18:24:31 GMT

Recreate them would be the quickest way

Re: oracle - copy index from one schema to another

Volker Borowski — Sun, 09 Sep 2007 06:17:14 GMT

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

Re: oracle - copy index from one schema to another

Stephen R Badgett — Mon, 10 Sep 2007 11:09:47 GMT

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