Pavan DBA's Blog

The DBA Knowledge Store

Script to find which Indexes need to rebuild

Posted by Pavan DBA on December 14, 2009

  vOwner   dba_indexes.owner%TYPE;            /* Index Owner            */
  vIdxName dba_indexes.index_name%TYPE;       /* Index 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
     FROM dba_indexes WHERE OWNER NOT LIKE ‘SYS%’;
  /* Define maximums. This section can be customized. */
  vMaxHeight := 3;
  vMaxDel    := 20;
  /* For every index, validate structure */
  OPEN cGetIdx;
     FETCH cGetIdx INTO vOwner,vIdxName;
     /* Open DBMS_SQL cursor */
     vCursor := DBMS_SQL.OPEN_CURSOR;
     /* Set up dynamic string to validate structure */
     vAnalyze := ‘ANALYZE INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ VALIDATE STRUCTURE’;
     vNumRows := DBMS_SQL.EXECUTE(vCursor);
     /* Close DBMS_SQL cursor */
     /* Does index need rebuilding?  */
     /* If so, then generate command */
     SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
     IF vDLfRows = 0 THEN         /* handle case where div by zero */
        vDLfPerc := 0;
        vDLfPerc := (vDLfRows / vLfRows) * 100;
     END IF;
     IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN 
        DBMS_OUTPUT.PUT_LINE(‘ALTER INDEX ‘ || vOwner || ‘.’ || vIdxName || ‘ REBUILD;’);
     END IF;
  CLOSE cGetIdx;

8 Responses to “Script to find which Indexes need to rebuild”

  1. Sridhar Sudanagunta said

    HI Pavan… Receiving the error like below, when running the above script. Your inputs will help.

    ERROR at line 1:
    ORA-01403: no data found
    ORA-06512: at line 35

  2. asifashiq said

    will this script will list the indexes with needs to be rebuild..
    what should i do to see those indexes?

  3. asifashiq said

    I would like to get the bad indexes in a particular index tablespace only…so how to proceed?

  4. jagat Mohanty said

    The following will list all the undocumented parameters.

    select ksppinm
    from x$ksppi
    where substr(ksppinm,1,1) = ‘_’;

Leave a Reply to asifashiq Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: