Identificando e compilando objetos inválidos no Oracle
Postado por Adauto Santos
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
