Thursday, January 1, 2009

Drop all indexes in a SCHEMA

How to drop all indexes in a SCHEMA
SQL>set pages 10000
SQL>set wrap off
SQL>set heading off
SQL>spool c:/indx.sql


SQL> select ' drop index ' || index_name ||';' from user_indexes;
drop index IND1;
drop index IND2;
drop index IND3;

SQL> spool off;

SQL>@indx.sql) in that particular schema in which you want to drop indexes.

A similar script can be used for synonyms especially when you import a schema into a different name than when it was exported.

No comments:

Post a Comment