Oracle Rebuild Index

CREATE OR REPLACE PACKAGE TPS_CASH_DB_MNT
AS


PROCEDURE DB_STATS(V_DEGREE IN VARCHAR2 DEFAULT 8);
PROCEDURE REBUILD_INDEX(V_ONLINE   IN VARCHAR2 DEFAULT NULL);
PROCEDURE REBUILD_INDEX(V_TAB_NAME IN VARCHAR2, 
                        V_ONLINE IN VARCHAR2 DEFAULT NULL
                       );


END TPS_CASH_DB_MNT;
/


CREATE OR REPLACE PACKAGE BODY TPS_CASH_DB_MNT
AS


PROCEDURE DB_STATS(V_DEGREE IN VARCHAR2 DEFAULT 8)
AS
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME          => USER,
                                 CASCADE          => TRUE,
                                 OPTIONS          => 'GATHER AUTO',
                                 ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                 METHOD_OPT       => 'for all columns size repeat',
                                 DEGREE           => V_DEGREE
                                );
END DB_STATS;


PROCEDURE REBUILD_INDEX(V_ONLINE IN VARCHAR2 DEFAULT NULL)
AS
ls_index_name  VARCHAR2(40)   ;
ls_part_type   VARCHAR2(40)   ;
ls_part_name   VARCHAR2(40)   ;
ls_sql         VARCHAR2(400)  ;
ls_nop         VARCHAR2(400)  ;
ld_start_date  DATE           ;
ln_SECONDS     NUMBER(10)     ;


CURSOR cur_indexes IS
  SELECT UI.INDEX_NAME,
         DECODE(UIP.COMPOSITE, 'NO', ' PARTITION ', 'YES', ' SUBPARTITION ', NULL) PARTITION_TYPE,
         DECODE(UIP.COMPOSITE,'NO',UIP.PARTITION_NAME, 'YES', UIS.SUBPARTITION_NAME, NULL) PARTITION_NAME
  FROM USER_INDEXES UI
  LEFT JOIN USER_IND_PARTITIONS UIP
  ON   UI.INDEX_NAME = UIP.INDEX_NAME
  LEFT JOIN USER_IND_SUBPARTITIONS UIS
  ON   UI.INDEX_NAME = UIS.INDEX_NAME
  WHERE UI.INDEX_NAME NOT LIKE 'SYS_IL%'
  AND   UI.INDEX_TYPE <> 'IOT - TOP'
  ORDER BY UI.TABLE_NAME, UI.INDEX_NAME, UIP.PARTITION_POSITION,
           UIS.PARTITION_NAME, UIS.SUBPARTITION_POSITION;


BEGIN
  SELECT SYSDATE INTO ld_start_date FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Rebuild INDEX at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
  FOR cur_1 IN cur_indexes LOOP
    ls_index_name := cur_1.INDEX_NAME;
    ls_part_type  := cur_1.PARTITION_TYPE;
    ls_part_name  := cur_1.PARTITION_NAME;


    ls_sql := 'ALTER INDEX '||ls_index_name||' REBUILD'||ls_part_type||ls_part_name||' PARALLEL '||V_ONLINE;
    ls_nop := 'ALTER INDEX '||ls_index_name||' NOPARALLEL ';
    --DBMS_OUTPUT.PUT_LINE(ls_sql||';');


    -- Execute
    BEGIN
      EXECUTE IMMEDIATE ls_sql;
      EXECUTE IMMEDIATE ls_nop;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(ls_sql||'; '||SQLERRM);
      DBMS_OUTPUT.PUT_LINE(ls_nop||'; '||SQLERRM);
    END;
  END LOOP;
  SELECT (SYSDATE - ld_start_date)*24*3600 INTO ln_SECONDS FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('COMPLETED... at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
END REBUILD_INDEX;


PROCEDURE REBUILD_INDEX(V_TAB_NAME IN VARCHAR2, V_ONLINE IN VARCHAR2 DEFAULT NULL)
AS
ls_index_name  VARCHAR2(40)   ;
ls_part_type   VARCHAR2(40)   ;
ls_part_name   VARCHAR2(40)   ;
ls_sql         VARCHAR2(400)  ;
ls_nop         VARCHAR2(400)  ;
ld_start_date  DATE           ;
ln_SECONDS     NUMBER(10)     ;


CURSOR cur_indexes IS
  SELECT UI.INDEX_NAME,
         DECODE(UIP.COMPOSITE, 'NO', ' PARTITION ', 'YES', ' SUBPARTITION ', NULL) PARTITION_TYPE,
         DECODE(UIP.COMPOSITE,'NO',UIP.PARTITION_NAME, 'YES', UIS.SUBPARTITION_NAME, NULL) PARTITION_NAME
  FROM USER_INDEXES UI
  LEFT JOIN USER_IND_PARTITIONS UIP
  ON   UI.INDEX_NAME = UIP.INDEX_NAME
  LEFT JOIN USER_IND_SUBPARTITIONS UIS
  ON   UI.INDEX_NAME = UIS.INDEX_NAME
  WHERE UI.INDEX_NAME NOT LIKE 'SYS_IL%'
  AND   UI.INDEX_TYPE <> 'IOT - TOP'
  AND   UI.TABLE_NAME = V_TAB_NAME
  ORDER BY UI.TABLE_NAME, UI.INDEX_NAME, UIP.PARTITION_POSITION,
           UIS.PARTITION_NAME, UIS.SUBPARTITION_POSITION;


BEGIN
  SELECT SYSDATE INTO ld_start_date FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Rebuild INDEX for '||V_TAB_NAME||' at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
  FOR cur_1 IN cur_indexes LOOP
    ls_index_name := cur_1.INDEX_NAME;
    ls_part_type  := cur_1.PARTITION_TYPE;
    ls_part_name  := cur_1.PARTITION_NAME;


    ls_sql := 'ALTER INDEX '||ls_index_name||' REBUILD'||ls_part_type||ls_part_name||' PARALLEL '||V_ONLINE;
    ls_nop := 'ALTER INDEX '||ls_index_name||' NOPARALLEL ';
    --DBMS_OUTPUT.PUT_LINE(ls_sql||';');


    -- Execute
    BEGIN
      EXECUTE IMMEDIATE ls_sql;
      EXECUTE IMMEDIATE ls_nop;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(ls_sql||'; '||SQLERRM);
      DBMS_OUTPUT.PUT_LINE(ls_nop||'; '||SQLERRM);
    END;
  END LOOP;
  SELECT (SYSDATE - ld_start_date)*24*3600 INTO ln_SECONDS FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('COMPLETED... at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
END REBUILD_INDEX;


END TPS_CASH_DB_MNT;
/