job rebuild index 11G

create or replace procedure sp_rebuildindex as
  vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  vIdxName dba_indexes.index_name%TYPE;       /* Index Name             */
  vTbName dba_indexes.table_name%TYPE;        /* Table Name             */
  vAnalyze VARCHAR2(100);                     /* String of Analyze Stmt */
  vCursor  NUMBER;                            /* DBMS_SQL cursor        */
  vNumRows INTEGER;                           /* DBMS_SQL return rows   */
  vHeight  index_stats.height%TYPE;           /* Height of index tree   */
  vLfRows  index_stats.lf_rows%TYPE;          /* Index Leaf Rows        */
  vDLfRows index_stats.del_lf_rows%TYPE;      /* Deleted Leaf Rows      */
  vDLfPerc   NUMBER;                          /* Del lf Percentage      */
  vMaxHeight NUMBER;                          /* Max tree height        */
  vMaxDel    NUMBER;                          /* Max del lf percentage  */
  CURSOR cGetIdx IS SELECT owner,index_name,table_name
     FROM dba_indexes WHERE OWNER NOT in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB','SYSMAN');
BEGIN
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;

  /* For every index, validate structure */
  OPEN cGetIdx;
  LOOP
     FETCH cGetIdx INTO vOwner,vIdxName,VTbName;
     EXIT WHEN cGetIdx%NOTFOUND;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
     DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     DBMS_SQL.CLOSE_CURSOR(vCursor);
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
        FROM INDEX_STATS;
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
     ELSE
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
EXECUTE IMMEDIATE 'ALTER INDEX ' || vIdxName || ' REBUILD ONLINE';
EXECUTE IMMEDIATE 'ANALYZE TABLE ' || vTbName || ' compute statistics';
EXECUTE IMMEDIATE 'ANALYZE INDEX '  || vIdxName || ' compute statistics';
     END IF;
  END LOOP;
  CLOSE cGetIdx;
END;
/


BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'REBUILDINDEX',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN sp_rebuildindex; END;',
   start_date      => '01-APR-12 09:00.00.00 AM ASIA/CALCUTTA',
  repeat_interval => 'FREQ=WEEKLY; BYDAY=SUN; BYHOUR=09; BYMINUTE=01;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'rebuild index');
END;
/

EXEC DBMS_SCHEDULER.RUN_JOB('REBUILDINDEX');