oracle script: 重建索引

笪6890

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;