Disable and later enable all table indexes in Oracle

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.

Pimple

Could you give a little more context? For example, did you try the following scenarios: remote-dba.net/teas_rem_util18.htm – Rabelaisian

Answer 1

Here's making the indexes unusable without the file:

DECLARE
  CURSOR  usr_idxs IS select * from user_indexes;
  cur_idx  usr_idxs% ROWTYPE;
  v_sql  VARCHAR2(1024);

BEGIN
  OPEN usr_idxs;
  LOOP
    FETCH usr_idxs INTO cur_idx;
    EXIT WHEN NOT usr_idxs%FOUND;

    v_sql:= 'ALTER INDEX ' || cur_idx.index_name || ' UNUSABLE';
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
  CLOSE usr_idxs;
END;

The rebuild would be similiar.

Dwyer

Could you post the rebuild for the noobs – Piquant

It's been a while, but you should be able to just replace ' UNUSABLE' with ' REBUILD'. – Dwyer

Answer 2

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.

Amann

Answer 3

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];

Snuffle

Answer 4

You can disable constraints in Oracle but not indexes. There's a command to make an index ununsable but you have to rebuild the index anyway, so I'd probably just write a script to drop and rebuild the indexes. You can use the user_indexes and user_ind_columns to get all the indexes for a schema or use dbms_metadata:

select dbms_metadata.get_ddl('INDEX', u.index_name) from user_indexes u;

Elephus

Answer 5

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.

Stack

Answer 6

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

Wheat

Answer 7

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;

Pimple

Answer 8

You should try sqlldr's SKIP_INDEX_MAINTENANCE parameter.

Dayton