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

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;

Answer 2

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.