Re: recreate all indexes on <tablename>

howard wrote:
> Hi,
>
> I've been asked by Oracle support to drop and recreate all the indexes
> on a table to deal with a ORA-600 error.
>
> Now, how would the database know what to re-create if I had just
> dropped the index?
>
> Do I just run "recreate all indexes on <tablename>" after I have
> dropped them?
>
> Thanks,
>
> Howard

Ummm, no. You either find the scripts used to create the indexes, you export the table and generate an indexfile which will contain all of the create index statements, you query user_indexes and user_ind_columns to manually create these statements or if you're using 9i or later you use the DBMS_METADATA.GED.DDL() function to generate ddl for the indexes associated with this table (this will require a query of USER_INDEXES to get the list to process):

set long 50000 head off pagesize 0 feedback off

select dbms_metadata.get_ddl('INDEX', index_name) from user_indexes
where table_name = '<tabname>'

spool <tabname>_idx.sql
/
spool off

Replace <tabname> with the table name of your choice.

Then drop the indexes and run this script you will have just created.

David Fitzjarrell Received on Tue Sep 12 2006 - 13:38:45 CDT