How to drop all indexes in a SCHEMA

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.

When login as GGADMIN with DBA privs:

set head off
select ' drop index ' || index_name ||';' from dba_indexes where TABLE_NAME like 'AR_%';

例子：
要drop以下表的index：
GGODS.AR_ADJUSTMENTS_ALL;
GGODS.AR_BATCH_SOURCES_ALL;
GGODS.AR_BATCHES_ALL;
GGODS.AR_CASH_RECEIPT_HISTORY_ALL;
GGODS.AR_CASH_RECEIPTS_ALL;
GGODS.AR_DISTRIBUTIONS_ALL;
GGODS.AR_MISC_CASH_DISTRIBUTIONS_ALL;
GGODS.AR_PAYMENT_SCHEDULES_ALL;
GGODS.AR_RECEIPT_CLASSES;
GGODS.AR_RECEIPT_METHODS;
GGODS.AR_RECEIVABLE_APPLICATIONS_ALL;
执行：
SQL> select ' drop index ' || index_name ||';' from dba_indexes where TABLE_NAME like 'AR_%';

结果为：
 drop index I_ARGUMENT1;
 drop index I_ARGUMENT2;
 drop index AR_ADJUSTMENTS_N10;
 drop index AR_ADJUSTMENTS_N11;
 drop index AR_ADJUSTMENTS_N13;
 drop index AR_ADJUSTMENTS_N14;
 drop index AR_ADJUSTMENTS_N15;
 drop index AR_ADJUSTMENTS_N16;
 drop index AR_ADJUSTMENTS_N17;
 drop index AR_ADJUSTMENTS_N2;
 drop index AR_ADJUSTMENTS_N3;
 drop index AR_ADJUSTMENTS_N4;
 drop index AR_ADJUSTMENTS_N5;
 drop index AR_ADJUSTMENTS_N6;
 drop index AR_ADJUSTMENTS_N7;
 drop index AR_ADJUSTMENTS_N8;
 drop index AR_ADJUSTMENTS_N9;
 drop index AR_ADJUSTMENTS_U1;
 drop index AR_ADJUSTMENTS_U2;
 drop index AR_BATCH_SOURCES_U1;
 drop index AR_BATCHES_N1;
 drop index AR_BATCHES_N2;
 drop index AR_BATCHES_N3;
 drop index AR_BATCHES_N4;
 drop index AR_BATCHES_N5;
 drop index AR_BATCHES_N6;
 drop index AR_BATCHES_N7;
 drop index AR_BATCHES_N8;
 drop index AR_BATCHES_N9;
 drop index AR_BATCHES_U1;
 drop index AR_CASH_RECEIPT_HISTORY_N1;
 drop index AR_CASH_RECEIPT_HISTORY_N10;
 drop index AR_CASH_RECEIPT_HISTORY_N2;
 drop index AR_CASH_RECEIPT_HISTORY_N3;
 drop index AR_CASH_RECEIPT_HISTORY_N4;
 drop index AR_CASH_RECEIPT_HISTORY_N6;
 drop index AR_CASH_RECEIPT_HISTORY_N8;
 drop index AR_CASH_RECEIPT_HISTORY_N9;
 drop index AR_CASH_RECEIPT_HISTORY_U1;
 drop index AR_CASH_RECEIPTS_CE_N1;
 drop index AR_CASH_RECEIPTS_N1;
 drop index AR_CASH_RECEIPTS_N10;
 drop index AR_CASH_RECEIPTS_N11;
 drop index AR_CASH_RECEIPTS_N12;
 drop index AR_CASH_RECEIPTS_N13;
 drop index AR_CASH_RECEIPTS_N14;
 drop index AR_CASH_RECEIPTS_N15;
 drop index AR_CASH_RECEIPTS_N2;
 drop index AR_CASH_RECEIPTS_N3;
 drop index AR_CASH_RECEIPTS_N5;
 drop index AR_CASH_RECEIPTS_N6;
 drop index AR_CASH_RECEIPTS_N7;
 drop index AR_CASH_RECEIPTS_N8;
 drop index AR_CASH_RECEIPTS_N9;
 drop index AR_CASH_RECEIPTS_U1;
 drop index AR_DISTRIBUTIONS_N1;
 drop index AR_DISTRIBUTIONS_N2;
 drop index AR_DISTRIBUTIONS_N3;
 drop index AR_DISTRIBUTIONS_U1;
 drop index AR_MISC_CASH_DISTRIBUTIONS_N1;
 drop index AR_MISC_CASH_DISTRIBUTIONS_N2;
 drop index AR_MISC_CASH_DISTRIBUTIONS_N3;
 drop index AR_MISC_CASH_DISTRIBUTIONS_N4;
 drop index AR_MISC_CASH_DISTRIBUTIONS_N5;
 drop index AR_MISC_CASH_DISTRIBUTIONS_U1;
 drop index AR_PAYMENT_SCHEDULES_N1;
 drop index AR_PAYMENT_SCHEDULES_N10;
 drop index AR_PAYMENT_SCHEDULES_N11;
 drop index AR_PAYMENT_SCHEDULES_N12;
 drop index AR_PAYMENT_SCHEDULES_N13;
 drop index AR_PAYMENT_SCHEDULES_N14;
 drop index AR_PAYMENT_SCHEDULES_N15;
 drop index AR_PAYMENT_SCHEDULES_N16;
 drop index AR_PAYMENT_SCHEDULES_N17;
 drop index AR_PAYMENT_SCHEDULES_N18;
 drop index AR_PAYMENT_SCHEDULES_N2;
 drop index AR_PAYMENT_SCHEDULES_N3;
 drop index AR_PAYMENT_SCHEDULES_N4;
 drop index AR_PAYMENT_SCHEDULES_N5;
 drop index AR_PAYMENT_SCHEDULES_N6;
 drop index AR_PAYMENT_SCHEDULES_N7;
 drop index AR_PAYMENT_SCHEDULES_N9;
 drop index AR_PAYMENT_SCHEDULES_V1;
 drop index AR_PAYMENT_SCHEDULES_U1;
 drop index AR_PAYMENT_SCHEDULES_U2;
 drop index AR_PAYMENT_SCHEDULES_U3;
 drop index AR_RECEIPT_CLASSES_U1;
 drop index AR_RECEIPT_METHODS_U1;
 drop index AR_RECEIPT_METHODS_U2;
 drop index AR_RECEIVABLE_APPLICATIONS_N1;
 drop index AR_RECEIVABLE_APPLICATIONS_N10;
 drop index AR_RECEIVABLE_APPLICATIONS_N11;
 drop index AR_RECEIVABLE_APPLICATIONS_N12;
 drop index AR_RECEIVABLE_APPLICATIONS_N13;
 drop index AR_RECEIVABLE_APPLICATIONS_N14;
 drop index AR_RECEIVABLE_APPLICATIONS_N15;
 drop index AR_RECEIVABLE_APPLICATIONS_N16;
 drop index AR_RECEIVABLE_APPLICATIONS_N2;
 drop index AR_RECEIVABLE_APPLICATIONS_N3;
 drop index AR_RECEIVABLE_APPLICATIONS_N4;
 drop index AR_RECEIVABLE_APPLICATIONS_N5;
 drop index AR_RECEIVABLE_APPLICATIONS_N6;
 drop index AR_RECEIVABLE_APPLICATIONS_N7;
 drop index AR_RECEIVABLE_APPLICATIONS_N8;
 drop index AR_RECEIVABLE_APPLICATIONS_N9;
 drop index AR_RECEIVABLE_APPLICATIONS_U1;
 drop index AR_RECEIVABLE_APPLICATIONS_V1;
107 rows selected.