Identificando e compilando objetos inválidos no Oracle

São muitos os motivos que levam uma objeto a ficar inválido, falha de codificação por parte dos programadores, perda de tabelas ou campos que são utilizados nos algoritmos, perda de dblinks entre os banco de dados, revogação de privilégios e muitos outros. O presente artigo irá explicar os quatro métodos de compilação que temos a disposição no Oracle: o automático, a compilação individual, a compilação por árvore de dependência e a compilação de todo o schema. Mas antes de detalhar as abordagens de compilação, temos que explicar o conceito de dependências diretas e indiretas.

Para o exemplos deste artigo iremos criar a tabela SALARIO_CARGO e a procedure PRC_RELATORIO_SALARIO, que apenas realiza a leitura da tabela e imprime o registros com o DBMS_OUTPUT.PUT_LINE.

-- Tabela SALARIO_CARGO 
CREATE TABLE SALARIO_CARGO
(
	NR_SEQ_CARGO NUMBER(5),
	VL_SALARIO NUMBER(12,2)
);

-- Procedure PRC_RELATORIO_SALARIO
CREATE OR REPLACE PROCEDURE PRC_RELATORIO_SALARIO AS
	CURSOR C_SALARIO IS
	SELECT NR_SEQ_CARGO, VL_SALARIO FROM SALARIO_CARGO;
BEGIN
	FOR REG IN C_SALARIO LOOP
		DBMS_OUTPUT.PUT_LINE('O SALÁRIO DO CARGO '|| REG.NR_SEQ_CARGO ||' É DE: '|| REG.VL_SALARIO);
	END LOOP;
END;

A Dependência Direta é quando uma tabela ou atributo são utilizados diretamente no escopo de uma consulta ou algoritmo PL/SQL, sendo assim a alteração em sua referência pode impactar no objeto. Para encontrar todos as dependências diretas de um determinado objeto utilizamos a view do dicionário de dados DBA_DEPENDENCIES, como no exemplo que se segue:

SQL> SELECT name, type, referenced_name, referenced_type FROM DBA_DEPENDENCIES WHERE name = 'PRC_RELATORIO_SALARIO';

NAME                    TYPE       REFERENCED_NAME                REFERENCED_TYPE
----------------------- ---------- ------------------------------ ------------------
PRC_RELATORIO_SALARIO   PROCEDURE  SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE
PRC_RELATORIO_SALARIO   PROCEDURE  STANDARD                       PACKAGE
PRC_RELATORIO_SALARIO   PROCEDURE  DBMS_OUTPUT                    SYNONYM
PRC_RELATORIO_SALARIO   PROCEDURE  SALARIO_CARGO                  TABLE

A Dependência Indireta é quando um objeto utiliza um outro objeto que por sua vez depende de outro. Por exemplo, uma PROCEDURE que depende do resultado de uma FUNCTION que faz a consulta em uma outra tabela. Esse tipo de relação é muito comum e pode se tornar facilmente em uma complexa árvore de referências.

Para analisar todas as dependências de um determinado objeto pode-se utilizar o comando DEPTREE_FILL, como na sequência de comandos a seguir.

-- Ambiente Linux
SQL>@$ORACLE_HOME/rdbms/admin/UTLDTREE.SQL

-- Ambiente Windows
SQL>@%ORACLE_HOME%/rdbms/admin/UTLDTREE.SQL

Executar a procedure deptree_fill, como no exemplo abaixo:
          BEGIN   
               deptree_fill('PROCEDURE','ADAUTO','PRC_RELATORIO_SALARIO ');  
          END;
    
     Ao executar a procedure deptree_fill, é preenchida uma tabela temporária com o nome ideptree, que contém o registro de todas as dependências do objeto que foi verificado. Para ver as dependências é só executar a query abaixo:
              SELECT * FROM ideptree;

DEPENDENCIES
--------------------------------------------------------------------------------
   CURSOR ."DECLARE BEGIN PRC_RELATORIO_SALARIO; END;"
PROCEDURE ADAUTO.PRC_RELATORIO_SALARIO

Compilação individual

Existem casos em que os objetos precisam ser compilados individualmente, pois a execução automática não pôde ser executada ou para quando os objetos precisam ser compilados rapidamente. Para isso podemos utilizar o comando que se segue para compilar a PROCEDURE PRC_RELATORIO_SALARIO:

ALTER PROCEDURE PRC_RELATORIO_SALARIO COMPILE;

A estrutura do comando é a seguinte:

ALTER tipo_do_objeto nome_do_objeto COMPILE [ou compile body para o caso de packages];

É possível encontrar todos os objetos inválidos da base (exceto os pertecentes ao SYS) e compilá-los com o comando que se segue. Trata-se de um recurso um útil para compilar muitos objetos de uma só vez.

SELECT 'ALTER '||OBJECT_TYPE||' '||OWNER||'.'||OBJECT_NAME||
	CASE
	   WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN ' COMPILE BODY;'
	   ELSE ' COMPILE;'
	END
  FROM DBA_OBJECTS
WHERE STATUS <> 'VALID'
  AND OWNER <> 'SYS'
  AND U.OBJECT_TYPE IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE');

Compilação por script SQL

O Oracle possui um script em seu diretório $ORACLE_HOME que realiza a compilação de todos os objetos inválidos presentes no Banco de Dados. O script fornece um relatório detalhado das operações executadas pelo processo de compilação.

-- Ambiente Linux
SQL>@$ORACLE_HOME/rdbms/admin/UTLRP.SQL

-- Ambiente Windows
SQL>@%ORACLE_HOME%/rdbms/admin/UTLRP.SQL

Compilação de todos os objetos de um schema

Para compilar todos os objetos de um schema também é possível utilizar um utilitário chamado COMPILE_SCHEMA como no exemplo que se segue. Esse recurso é muito útil para cenários em que muito objetos de um mesmo schema estão inválidos e precisam ser compilados.

SQL> DBMS_UTILITY.COMPILE_SCHEMA('SCHEMA');

Outra opção é utilizar a procedure RECOMP_PARALLEL da package UTL_RECOMP, no qual é possível compilar todos os objetos de um determinado usuário (ou todos os usuários com o NULL) utilizando o paralelismo para tornar o processo mais rápido.

DECLARE BEGIN UTL_RECOMP.RECOMP_PARALLEL(2,'ADAUTO');END;

Compilação automática

O Oracle tende a compilar automaticamente os objetos inválidos à partir da sua execução. Por exemplo, imagine o cenário em que o programador por engano invalide uma procedure ao excluir por engano uma coluna de uma tabela. Contudo, ao se dar conta do erro o programador retorna rapidamente a coluna, mesmo asism a procedure continuará inválida. Sendo assim, o Oracle irá verificar o seu status e forçará a compilação automática, e esse processo fará com a procedure volte a se tornar válida. Essa operação será demonstrada no sequência de comandos a seguir:

SQL> SELECT STATUS, OBJECT_NAME FROM DBA_OBJECTS DO WHERE DO.OBJECT_NAME = 'PRC_RELATORIO_SALARIO';

STATUS  OBJECT_NAME
------- ----------------------
VALID   PRC_RELATORIO_SALARIO

SQL> ALTER TABLE SALARIO_CARGO DROP (VL_SALARIO);

SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME = 'PRC_RELATORIO_SALARIO';

STATUS    OBJECT_NAME
--------- ----------------------
INVALID   PRC_RELATORIO_SALARIO

Por fim, iremos adicionar novamente a coluna VL_SALARIO e verificar o status na DBA_OBJECTS e forçar uma compilação automática da procedura por meio de sua chamada em um bloco anônimo.

SQL> ALTER TABLE SALARIO_CARGO ADD (VL_SALARIO NUMBER(12,2));

SQL> SELECT OBJECT_NAME, STATUS FROM DBA_OBJECTS WHERE OBJECT_NAME = 'PRC_RELATORIO_SALARIO';

STATUS    OBJECT_NAME
--------- ----------------------
INVALID   PRC_RELATORIO_SALARIO

SQL> DECLARE BEGIN PRC_RELATORIO_SALARIO; END;

SELECT STATUS, OBJECT_NAME FROM DBA_OBJECTS DO WHERE DO.OBJECT_NAME = 'PRC_RELATORIO_SALARIO';

STATUS  OBJECT_NAME
------- ----------------------
VALID   PRC_RELATORIO_SALARIO

Nota: Os termos schema, owner e usuário são exatamente sinônimos, mas podem ser utilizados para se referir ao agrupamentos dos objetos de um usuário no Oracle.

Referências

https://oracle-base.com/articles/misc/recompiling-invalid-schema-objects
http://www.orafaq.com/node/2008
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/rilin/recompiling-all-invalid-objects.html
http://www.fabioprado.net/2011/05/recompilando-objetos-invalidos-no.html

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!