Reverse engineering index scripts

[Padre764]

Hello,

I have about 60 tables with about 70 indexes between them. I need to add a new field to all of the tables. Once the new field is on all of the tables, I need to recreate all of the existing indexes using the new field instead of one of the existing fields. I.E. if the existing index is:
create index a_idx on p.table
(d, e, f)
the new one needs to be;
create index a_idx_1 on p.table
(x, e, f)
(In every existing index field d has to replaced with field x.)

Does anyone have any ideas? I have TOAD and could just cut and paste the scripts that it generates but I would have to do this a table at a time. It just seems that there has to be a better way.

Thanks,
Padre

 

[carp]

Lucky you! Oracle has provided the DBMS_METADATA package which should help you do just what you want!
Let's assume the column name you want to replace is called "OLD_COLUMN". Then you can create a script as follows:

SET PAGESIZE 0
SET LONG 100000
SPOOL INDEX_SCRIPT.SQL
SELECT dbms_metadata.get_dependent_ddl('INDEX',table_name)  FROM dba_ind_columns
WHERE column_name = 'OLD_COLUMN';
SPOOL OFF

Now you can go into the INDEX_SCRIPT.SQL text file with the text editor of your choice. Eliminate any extraneous junk (such as the query) from the top and bottom. Then do a blanket replace, substituting "NEW_COLUMN" for "OLD_COLUMN".
You now have a script you can run after your tables are restructured.

Elbert, CO
1141 MST

 

[Padre764]

carp,

Thanks. It seems to work but, I am getting duplicates in what is being returned.

Padre

 

[carp]

Yes, I just thought of that too. Also, you are probably getting double quotes and no semicolon! This will get you around that:

select 
REPLACE(dbms_metadata.get_dependent_ddl('INDEX',table_name),'"','')||';'
from (SELECT DISTINCT table_name FROM user_ind_columns where column_name = 'OLD_COLUMN');

Elbert, CO 1309 MDT

 

[Padre764]

Carp,

I got around the duplicates by adding the table_name to the where clause but, now I see that I getting every index on the table regardless of the column_name that I specify. Any ideas on that?

Thanks,
Padre

 

[carp]

Nope. The way the package works is that if you ask for the indexes on a table, it gives them to you. This is where it will get dirty - you'll just have to highlight and delete the indexes you don't want to recreate.

Of course, if you just ran the script as-is the statements that try to create an already-existant index will just error out.

Elbert, CO
1416 MDT