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
SQL>@indx.sql
–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.
