-- During large volume data loads, the indexes may need to be 
-- disabled before load and enabled after the completion of load.
 
-- Procedure disable_index will disable all non-unique indexes

CREATE OR REPLACE PROCEDURE disable_index(i_owner IN VARCHAR2,
                                     i_table_name IN VARCHAR2) AS
 v_table_name  VARCHAR2(30) := UPPER(i_table_name);
 v_owner       VARCHAR2(30) := UPPER(i_owner);
BEGIN
 SELECT DECODE(v_owner,NULL,user,v_owner) INTO v_owner
 FROM   DUAL;

 FOR idx IN (SELECT index_name FROM all_indexes
             WHERE  table_name = v_table_name
             AND    owner = v_owner
             AND    uniqueness = 'NONUNIQUE')
 LOOP
   EXECUTE IMMEDIATE 'ALTER INDEX '||v_owner||'.'||
                              idx.index_name||' UNUSABLE';
 END LOOP;
 
 EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unuasble_indexes=true';

END disable_index;
/


-- Procedure enable_index will enable all non-unique indexes

CREATE OR REPLACE PROCEDURE enable_index(i_owner IN VARCHAR2,
                                    i_table_name IN VARCHAR2) AS
 v_table_name  VARCHAR2(30) := UPPER(i_table_name);
 v_owner       VARCHAR2(30) := UPPER(i_owner);
BEGIN
 SELECT DECODE(v_owner,NULL,user,v_owner) INTO v_owner
 FROM   DUAL;

 EXECUTE IMMEDIATE 'ALTER SESSION SET skip_unuasble_indexes=false';

 FOR idx IN (SELECT index_name FROM all_indexes
             WHERE  table_name = v_table_name
             AND    owner = v_owner
             AND    uniqueness = 'NONUNIQUE')
 LOOP
   EXECUTE IMMEDIATE 'ALTER INDEX '||v_owner||'.'||
                    idx.index_name||' REBUILD PARALLEL NOLOGGING';

   EXECUTE IMMEDIATE 'ALTER INDEX '||v_owner||'.'||
                    idx.index_name||' NOPARALLEL';
 END LOOP;

END enable_index;
/