Notas My Oracle Support "Seguridad"

Notas My Oracle Support "Seguridad"

What Privileges Are Revoked During Database Vault Installation ? [ID 550863.1]

DETECTANDO FRAGMENTACION DE INDICES

En oracle podemos revisar informacion sobre un indice en particular ejecutando el comando de SQLPLUS:

VALIDATE INDEX ;

La informacion obtenida se guarda en la Tabla DBA_INDEXES, sin embargo esta tabla solo contiene una fila. Por ello si necesitamos revisar una gran cantidad de Indices se vuelve una tarea tediosa.

A continuacion un script que nos permite agilizar este proceso, revisando todos los indices de un equema en particular.



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!!!