How to drop all indexes in a SCHEMA : Oracle 10g

How to drop all indexes in a SCHEMA

–Login as the schema user whose indexes you want to drop

SQL>set pages 10000
SQL>set wrap off
SQL>set heading off
SQL>spool /u2/scripts/idxswx.txt

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

SQL> spool off;

— This will spool the file to the set location and now run the Script as the schema user
SQL> conn scott/tiger


–in the 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.

Leave a Reply