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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s