Verificar se Indices devem passar por Rebuild

(1) -- CRIAR A TABELA ONDE ARMAZENAMENTOS AS INFORMAÇÕES

CREATE TABLE MMED_INDICE_STATUS (INDEX_NAME VARCHAR(100),PCT_DELETED NUMBER,PCT_USED NUMBER,BLOCK NUMBER,DTEXECUAO DATE DEFAULT SYSDATE) ;

ALTER TABLE MMED_INDICE_STATUS ADD CONSTRAINT PK_MMED_IND_STATUS PRIMARY KEY (INDEX_NAME,DTEXECUAO) ;


(2) -- RODAR A QUERY ABAIXO E PEGAR O RESULTADO DELA PARA EXECUAR

SELECT COMM
FROM(
SELECT 1 AS ID,
ROWNUM AS ID2,
'ANALYZE INDEX ' ||INDEX_NAME || ' VALIDATE STRUCTURE ; ' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'

UNION ALL

SELECT 2 AS ID,
ROWNUM ID2,
'INSERT INTO MMED_INDICE_STATUS SELECT NAME,TRUNC(100*(NVL(DEL_LF_ROWS,1)/NVL(LF_ROWS,1))) PCT_DELETED, PCT_USED ,BLOCKS ,SYSDATE FROM INDEX_STATS ;' AS COMM
FROM DBA_INDEXES
WHERE OWNER = '&user'
ORDER BY ID2,ID
)

(3) -- EXECUTAR O RESULTADO DO PASSO 3 E DAR COMMIT .

COMMIT ;



(4) -- VERIFICAR OS INDICES COM MAIS DE 30 % DE FRAGMENTAÇÃO

SELECT DISTINCT A.* ,B.TABLE_NAME
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100
ORDER BY PCT_DELETED DESC


(5) -- AJUSTA OS INDICES QUE FORAM ENCONTADOS COM FRAGMENTAÇÃO MAIOR QUE 20 %

-- RODAR A QUERY ABEIXO E DEPOIS EXECUTAR O RESULTADO

SELECT DISTINCT 'ALTER INDEX ' || A.INDEX_NAME || ' REBUILD TABLESPACE TSP_CLINICA_INDEX ; ' AS RESULTADO
FROM MMED_INDICE_STATUS A
JOIN DBA_INDEXES B ON B.INDEX_NAME = A.INDEX_NAME
WHERE PCT_DELETED > 20 AND PCT_DELETED < 100

Views importantes

Segue uma lista de Views importantes que podem ser utilizadas mesmo com o banco de dados não estando no estado de open .

Views que podem ser utilizadas no estado de NOMOUNT

V$PARAMETER : Mostra informações sobre os parametros de inicialização do sistema.
V$SGA : Informações sobre a SGA da Instancia.
V$OPTION : opções da instancia ,exemplo se está em um ambiente em cluster ou se está utilizando ASM.
V$SESSION : informações sobre as sessões existentes na Instancia.
V$VERSION : Informações sobre a versões dos aplicativos que juntos formas o seu RDBMS.
V$INSTANCE : Informações básicas da instancia oracle.

Views que podem ser utilizados no estado de MOUNT.

V$CONTROLFILE : informações sobre os arquivos de controle.
V$DATAFILE : informações sobre os data files componentes das Tablespaces.
V$LOGFILE : informações dos arquivos de Redo Log que estiverem online.

Documentação Oracle

Muitos não conhecem mas a oracle tem um site oficial e aberto com toda documentação disponível sobre os seus produtos.Vejo alguns DBA reclamarem que este site não é tão atualizado como o MetaLink mas acredito que seja uma boa fonte de ganho de conhecimento.Segue a dica :

http://www.oracle.com/technetwork/indexes/documentation/index.html