Rebuilding indices tied to primary key constraints

Maria Miller

Member
I've noticed that when I use Oracle exp/imp to refresh business data in Dev from Prod, some indices that are tied to primary key constraints get created in the 'T' tablespace. I'd like to put these in the proper 'I' tablespace. Because the index is tied to a primary key constraint, I first need to drop the primary key constaint before I can drop and rebuild the index. Then I need to add the primary key constraint.

How are people handling rebuilding indices that are tied to primary key constraints? I've been trying to figure out the simplest way to deal with these indices.

Any comments are much appreciated.

Maria
 
Hi Maria,

When doing your oracle imports - specify Parameters CONSTRAINTS=N INDEXES=N. This way no indices are created - just the tables.

Then run imp again, this time using the Parameter INDEXFILE=xxxx.sql. This causes imp to only create a text file of the SQL needed to recreate indices and constraints.

Edit the generated SQL file using global change and replace to make sure the schema and tablespace names are what you want. Then execute the sql script from sqlplus or worksheet.

We have this all scripted in our Unix environment.

Regards,
 
Larry,

Thanks for pointing out imp with constraints=n. This was the piece what I was missing.

Maria
 
Back
Top