oracle script:查看索引状态

imerrvtvys

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;