oracle script:查看索引状态

REM =============================================================
REM  index_status.sql
REM  2013-11-14
REM  Database Version : 7.3.X and above.
REM =============================================================

prompt
Accept Schema Char Prompt 'Schema name (% allowed) : ';
prompt

set serveroutput on;
set verify off;
set linesize 140;
declare
  c_name        INTEGER;
  ignore        INTEGER;
  height        index_stats.height%TYPE := 0;
  lf_rows       index_stats.lf_rows%TYPE := 0;
  del_lf_rows   index_stats.del_lf_rows%TYPE := 0;
  distinct_keys index_stats.distinct_keys%TYPE := 0;
  Cursor C_Indx Is
    select owner, table_name, index_name,temporary temp
      from dba_indexes
     where owner like upper('&schema')
       And Owner Not In ('SYS', 'SYSTEM')
       and table_name not like 'BIN$%';
begin
  Dbms_Output.Enable(1000000);
  Dbms_Output.Put_Line('Owner           Index Name                              % Deleted Entries Height Distinctiveness  temp');
  dbms_output.put_line('--------------  ---------------------------------            ------------  -----           -----  ----');

  c_name := DBMS_SQL.OPEN_CURSOR;
  For R_Indx In C_Indx Loop
    Dbms_Sql.Parse(c_name,
                   'analyze index ' || r_indx.owner || '.' || r_indx.index_name || ' validate structure',
                   DBMS_SQL.NATIVE);
    ignore := DBMS_SQL.EXECUTE(c_name);

    select HEIGHT,
           decode(LF_ROWS, 0, 1, LF_ROWS),
           DEL_LF_ROWS,
           decode(DISTINCT_KEYS, 0, 1, DISTINCT_KEYS)
      into height, lf_rows, del_lf_rows, distinct_keys
      from index_stats;
    
   
      dbms_output.put_line(rpad(r_indx.owner, 16, ' ') ||
                           rpad(r_indx.index_name, 40, ' ') ||
                           Lpad(Round((Del_Lf_Rows / Lf_Rows) * 100, 3),17,' ') || 
                           Lpad(Height - 1, 7, ' ') ||
                           Lpad(Round((Lf_Rows - Distinct_Keys) * 100 /Lf_Rows,3),16,' ')||
                           Lpad(r_indx.temp, 6, ' ')
                          );
   
  end loop;
  DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
Set Verify On;