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.

Solution 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.

Solution 2

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

Solution 3

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;

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Worthy7]

Could you post the rebuild for the noobs

[jmc]

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