Disable and later enable all table indexes in Orac

Question

How would I disable and later enable all indexes in a given schema/database in Oracle?

Note: This is to make sqlldr run faster.

Answer 1

combining 3 answers together: (because a select statement does not execute the DDL)

set pagesize 0

alter session set skip_unusable_indexes = true;
spool c:\temp\disable_indexes.sql
select 'alter index ' || u.index_name || ' unusable;' from user_indexes u;
spool off
@c:\temp\disable_indexes.sql

Do import...

select 'alter index ' || u.index_name || 
' rebuild online;' from user_indexes u;

Note this assumes that the import is going to happen in the same (sqlplus) session.
If you are calling "imp" it will run in a separate session so you would need to use "ALTER SYSTEM" instead of "ALTER SESSION" (and remember to put the parameter back the way you found it.

Answer 2

Combining the two answers:

First create sql to make all index unusable:

alter session set skip_unusable_indexes = true;
select 'alter index ' || u.index_name || ' unusable;' from user_indexes u;

Do import...

select 'alter index ' || u.index_name || ' rebuild online;' from user_indexes u;

Answer 3

You should try sqlldr's SKIP_INDEX_MAINTENANCE parameter.

Answer 4

From here: http://forums.oracle.com/forums/thread.jspa?messageID=2354075

alter session set skip_unusable_indexes = true;

alter index your_index unusable;

do import...

alter index your_index rebuild [online];

Answer 5

If you're on Oracle 11g, you may also want to check out dbms_index_utl.

Answer 6

If you are using non-parallel direct path loads then consider and benchmark not dropping the indexes at all, particularly if the indexes only cover a minority of the columns. Oracle has a mechanism for efficient maintenance of indexes on direct path loads.

Otherwise, I'd also advise making the indexes unusable instead of dropping them. Less chance of accidentally not recreating an index.