Индексы требующие перестроения

declare
  v_Owner    dba_indexes.owner%type;        -- Setup Index Owner
  v_IdxName  dba_indexes.index_name%type;   -- Setup Index Name
  
  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(
    pc_owner dba_indexes.owner%type,
    pc_index dba_indexes.owner%type
  )
  is select owner,index_name
  from dba_indexes
  where owner = nvl(pc_owner, owner)
    and index_name = nvl(pc_index, index_name)
    and index_type='NORMAL'
    order by owner, index_name;
begin
  -- Customize maximums:
  vMaxHeight := 3;
  vMaxDel    := 20;
  
  -- Customize indexes:
  v_Owner   := 'OWNER_NAME';
  v_IdxName := 'INDEX_NAME';
  
  -- Open Indexes Cursor:
  open cGetIdx(v_Owner, v_IdxName);
  
  -- For every index, validate structure:
  loop
    fetch cGetIdx
    into vOwner, vIdxName;
    
    exit when cGetIdx% notfound;
    
    -- Try Analyze Index:
    begin
      -- 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;
      
      -- Handle case where div by zero:
      if vDLfRows = 0 then 
        vDLfPerc := 0;
      else
        vDLfPerc := trim((vDLfRows / vLfRows) * 100);
      end if;
      
      if (vDLfPerc > vMaxDel) then
        dbms_output.put_line(
          'alter index ' || vOwner || '.' || vIdxName ||
          ' rebuild online compute statistics;' || ' -- ' || to_char(vDLfPerc, '09') || '%'
        );
      end if;
    exception
      when others then
        dbms_output.put_line( '-- ' || vOwner || '.' || vIdxName || ': ' || sqlerrm());
    end;
  end loop;
  
  -- Close Indexes Cursor:
  close cGetIdx;
end;
/