oracle script: 重建索引

REM =============================================================
REM  rebuild_index.sql
REM  2013-11-14
REM  Database Version : 7.3.X and above.
REM  SPOOLFILE easy_rebuild_index.sql
REM
REM  Index is considered as candidate for rebuild when :
REM   - when deleted entries represent delete_percent (default 0.2) or more of the current entries
REM   - when the index depth is more then degree_level (default 5) levels.(height starts counting from 1)
REM =============================================================

prompt
accept Schema Char Prompt 'Schema name (% allowed) : ';
accept delete_percent Number Default 0.2 Prompt 'Delete percent (0.2) : ';
accept degree_level Number Default 5 Prompt 'Degree level (5) : ';
accept ss_online Char Default 'Y' Prompt 'rebuild online(Y/N): ';
accept is_logging Char default 'N' Prompt 'rebuild logging(Y/N): ';
prompt

spool easy_rebuild_index.sql;
set serveroutput on;
set verify off;
Declare
  vc_rebuild    varchar2(200);
  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
      from dba_indexes
     where owner like upper('&schema')
       And Owner Not In ('SYS', 'SYSTEM')
       And Table_Name Not Like 'BIN$%'
       and temporary = 'N';
begin
  dbms_output.enable(1000000);

  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;
    
    If (Height > °ree_level) Or ((Del_Lf_Rows / Lf_Rows) > &delete_percent) Then
      select 
      'alter index'|| R_Indx.Owner||'.'||R_Indx.Index_Name||' rebuild '
      ||Decode(Upper('&is_Online'),'Y',' online ','')
      ||Decode(Upper('&Is_Logging'),'N',' nologging ',' logging ')
      ||';'
      Into Vc_Rebuild From Dual;
      dbms_output.put_line(Vc_Rebuild);
    end if;

  end loop;
  DBMS_SQL.CLOSE_CURSOR(c_name);
end;
/
spool off;
Set Verify On;