Si necesitamos revisar una gran cantidad de Indices se vuelve una tarea tediosa.
-- by Fabian Crea - DBA ORACLE
prompt -- Drop and create temporary table to hold stats...
drop table my_index_stats
/
create table my_index_stats (
index_name varchar2(30),
height number(8),
del_lf_rows number(8),
if_rows number(8),
distinct_keys number(8),
rows_per_key number(10,2),
blks_gets_per_access number(10,2)
)
/
prompt -- Save script which we will later use to populate the above table...
insert into my_index_stats
select NAME, HEIGHT, DEL_LF_ROWS,LF_ROWS, DISTINCT_KEYS, ROWS_PER_KEY,
BLKS_GETS_PER_ACCESS
from INDEX_STATS
-- Note this open line...
save /tmp/save_index_stats.sql replace
prompt
prompt -- Spool listing with validate commands...
col line1 newline
col line2 newline
col line3 newline
set pagesize 0
set echo off
set termout off
set trimspool on
set feed off
set linesize 200
spool /tmp/validate_indexes.sql
select 'prompt Process table '||owner||'.'||table_name||
', index '||index_name||'...' line1,
'validate index '||owner||'.'||index_name||';' line2,
'@/tmp/save_index_stats.sql' line3
from sys.dba_indexes where owner = 'SCOTT'
order by table_name, index_name
/
spool off
set termout on
set feed on
prompt
prompt -- Run script to validate indexes...
@/tmp/validate_indexes.sql
prompt -- Print nice report...
set pagesize 50000
set trimspool on
col height format 99999
col del_rows format 9999999
col rows/key format 999999.9
spool idxfrag.lst
select INDEX_NAME, HEIGHT, DEL_LF_ROWS "DEL_ROWS",LF_ROWS, DISTINCT_KEYS "DIST KEYS",
ROWS_PER_KEY "ROWS/KEY",
BLKS_GETS_PER_ACCESS "BLKS/ACCESS"
from MY_INDEX_STATS
/
spool off
-- Cleanup
drop table my_index_stats
/
prompt
prompt Report is in idxfrag.lst
prompt Done!!!
