recriar indices

Eu ja tinha feito algumas linhas de codigo para o REBUILD do indice mas pesquisando encontrei um blog do Guilherme e achei interessante postar aqui a dica dele.

Essa package criada por mim possibilita a anlise de ínidices para saber se eles precisam sofrer o rebuild. Caso seja necessário, tem um procedimento que automatiza essa tarefa também.

CREATE
PACKAGE manutencao_indice
IS
PROCEDURE cria_tabela;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2);

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER);

PROCEDURE analisa_todos;

END; — Package spec
/

CREATE
PACKAGE BODY manutencao_indice

IS

PROCEDURE cria_tabela
IS
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE ‘||USER||’.DBA$INDEX_STATS
(DONO VARCHAR2(30),
NOME_INDICE VARCHAR2(30),
PCT_DEL NUMBER(5,2),
QUANDO DATE)’;
END;

PROCEDURE analisa_indice(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
sql_stmt VARCHAR2(200);
CURSOR cur_stats
IS
SELECT (b.del_lf_rows/(DECODE(b.lf_rows,0,1)))*100 pct_delete
FROM index_stats b
WHERE b.name = indice;

BEGIN
EXECUTE IMMEDIATE ‘ANALYZE INDEX ‘||dono||’.’||indice||’ VALIDATE STRUCTURE’;
FOR rec_stats IN cur_stats LOOP
sql_stmt :=’INSERT INTO ‘||USER||’.dba$index_stats VALUES (:1, :2, :3, :4)’;
EXECUTE IMMEDIATE sql_stmt USING dono, indice, rec_stats.pct_delete, SYSDATE;
END LOOP;
COMMIT;
END;

PROCEDURE analisa_schema
(dono IN VARCHAR2 DEFAULT USER)
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes
WHERE owner = dono;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE analisa_todos
IS
CURSOR cur_indices
IS
SELECT owner dono, index_name nome
FROM dba_indexes;
BEGIN
FOR rec_indices IN cur_indices LOOP
analisa_indice(rec_indices.dono, rec_indices.nome);
END LOOP;
END;

PROCEDURE rebuild_index(
dono IN VARCHAR2 DEFAULT USER,
indice IN VARCHAR2)
IS
BEGIN
EXECUTE IMMEDIATE ‘ALTER INDEX ‘||dono||’.’||indice||’ REBUILD ONLINE’;
END;

END;
/

Fonte: http://oraclemais.blogspot.com/

Rebuild dos indices do Oracle Portal

Login como PORTAL

set pagesize 0
set linesize 250

spool rebuild_index1.sql
select ‘alter index ‘||INDEX_NAME||’ rebuild;’
from dba_indexes
where TABLE_NAME in (‘WWNLS_STRINGS$’,
‘WWPOB_ITEM$’,
‘WWPOB_PAGE_CUSTOMIZATION$’,
‘WWPOB_PAGE$’,
‘WWPOB_PORTLET_INST$’,
‘WWV_THINGS’,
‘WWV_DOCINFO’,
‘WWV_SEC_SYS_PRIV$’,
‘WWSEC_FLAT$’,
‘WWSBR_SITES$’,
‘WWSBR_URL$’,
‘WWDOC_DOCUMENT$’)
and OWNER = ‘PORTAL’
and INDEX_NAME not like ‘%_PK’ — exclude the primary keys indexes
and INDEX_NAME not like ‘WWSBR%CTX%’ — exclude the intermedia indexes
and INDEX_NAME not like ‘SYS_%’ — exclude the LOB indexes (check Note #159995.1 why)
;
spool off

spool rebuild_index2.sql
select ‘alter index ‘||INDEX_NAME||’ rebuild;’
from dba_indexes
where TABLE_NAME like ‘WWUTL%’
and OWNER = ‘PORTAL’
and INDEX_NAME not like ‘%_PK’ — exclude the primary keys indexes
and INDEX_NAME not like ‘SYS_%’ — exclude the LOB indexes (check Note #159995.1 why)
;
spool off

execute rebuild_index1.sql
execute rebuild_index2.sql