How would I disable and later enable all indexes in a given schema/database in Oracle?
Note: This is to make sqlldr run faster.
This question is related to
sql
oracle
sql-loader
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.
If you're on Oracle 11g, you may also want to check out dbms_index_utl.
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;
You should try sqlldr's SKIP_INDEX_MAINTENANCE parameter.
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;
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.
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];
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.
Source: Stackoverflow.com