Resolvendo o erro ORA-08104: this index object is being online built or rebuilt

Os índices dentro de um banco de dados desempenham um papel fundamental na melhoria de performance de consultas, e operações de criação e rebuild de índices são muito comuns no dia a dia de um DBA Oracle. Estas operações, entretanto, podem demorar um tempo relativamente grande quando estamos lidando com índices de tabelas muito grandes. E o que acontece quando um erro interrompe a execução do comando de criação ou rebuild de um índice?

ORA-08104: o problema

Em situações onde o comando de criação ou rebuild de índice é interrompido abruptamente, seja pela sessão executora ser finalizada, queda de rede, ou até mesmo a interrupção do comando com CTRL + C, podemos ter um cenário onde o índice não teve a operação concluída com sucesso, mas ainda consta no dicionário de dados do banco Oracle.

Por exemplo, suponhamos que o usuário está criando o índice com o comando abaixo, e durante a execução de criação, ele interrompeu o comando com CTRL + C, pois a criação estava demorando demais:

Ao tentar executar o comando de criação novamente, agora utilizando paralelismo, para diminuir o tempo de criação, ele obteve o erro, como se o índice já existisse no banco:

Na tentativa de correção, o usuário então tenta realizar o drop do índice, e recebe um ORA-08104:

Nesse cenário, o índice em questão está “preso” e consta no dicionário de dados.

DBMS_REPAIR: a solução

Quando esse tipo de situação acontece temos basicamente duas alternativas de resolução. A primeira é esperar pelo processo SMON, que roda a cada 60 minutos no banco, realizando a limpeza desses índices que tiveram a operação de criação ou rebuild interrompidos, porém essa limpeza só ocorrerá caso nenhuma transação esteja sendo executada contra a tabela base do índice no momento da tentativa de limpeza. Ou seja, a limpeza só ocorrerá se não houverem transações não commitadas utilizando a tabela base.

A segunda alternativa é realizar a limpeza manualmente utilizando a package DBMS_REPAIR, que contém procedures e functions que permitem identificar e reparar corrupções. Para este cenário, podemos utilizar a function ONLINE_INDEX_CLEAN, que faz a limpeza de criações ou rebuilds de índices interrompidos. Essa função retorna FALSE caso a limpeza não tenha sido possível, devido à tabela base estar sendo utilizada por uma transação, por exemplo, ou TRUE, caso a limpeza tenha sido executada com sucesso. Como argumento para esta function, passamos o object id do índice que teve a criação ou rebuild interrompido. Este object id está presente na mensagem de erro do ORA-08104. Para o nosso cenário, por exemplo, podemos verificar que o object id retornado pela mensagem de erro corresponde ao nosso índice através da consulta:

Para rodar a function, executamos o seguinte código PL/SQL:

DECLARE
    isClean BOOLEAN;
BEGIN
    isClean :=FALSE;
WHILE isClean=FALSE
    LOOP
        isClean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(89547);
        DBMS_LOCK.SLEEP(5);
    END LOOP;
END;
/

Esse trecho usará a variável “isClean” para armazenar o valor retornado pela execução da function, à qual repassamos o object id do índice a ser limpo. Essa variável controlará o loop while, que reexecutará o trecho após 5 segundos caso o retorno seja FALSE, ou seja, que o índice em questão não tenha sido limpo.

Após a execução do código acima, podemos executar novamente a consulta na tabela OBJ$, verificando que agora não temos mais nenhum objeto com este object id:

Após a limpeza, a execução do índice ocorre normalmente:

Se você trabalha em ambientes Oracle e tem demandas de criação ou rebuild de grandes índices, já salva o procedimento!

Um grande abraço e até a próxima!