Recompilando objetos inválidos no Oracle Database

Olá Pessoal,

     No artigo de hoje irei comentar sobre uma característica muito interessante do Oracle Database 10G, a recompilação automática de objetos inválidos. Irei dividir este assunto em três partes: dependências locais, depedências remotas e recompilando os objetos inválidos.  

  

1- Dependências locais

  

     Quando um objeto (Ex.: tabela) do Banco de Dados é alterado, todos os objetos que dependem dele, direta e indiretamente, tornam-se inválidos. Um objeto depende de outro quando o primeiro objeto referencia outro objeto.
 
      Exemplos:
          1- Uma visão chamada VW_EMPLOYEES que faz um SELECT na tabela EMPLOYEES possui uma relação de dependência direta com a tabela EMPLOYEES.
          2- Uma stored procedure chamada SP_EMPLOYEES_RETURN que tem código que referência a visão VW_EMPLOYEES possui uma relação de dependência direta com a visão VW_EMPLOYEES e uma relação de dependência indireta com a tabela EMPLOYEES.

  

     
      No Oracle Database 10G a invalidação de objetos não é motivo de preocupação quando houver somente depedências locais. Na 1ª chamada após os objetos serem inválidados, eles são recompilados e tornam-se válidos novamente, exceto quando a alteração no objeto referenciado exclui algo que estava sendo referenciado. Por exemplo, ao tentar recompilar uma visão que referencia uma coluna que foi excluída de uma tabela, a visão continuará inválida, pois no momento da compilação o Oracle verificará que a coluna não existe mais na tabela referenciada e isso impedirá a compilação da visão!

Obs.:  A recompilação automática dos objetos dependentes implica em um tempo de resposta maior na 1º execução destes objetos (após a invalidação).

  

                

2- Dependências remotas

    O que muda em dependências remotas é que os objetos inválidos, referenciando objetos remotos, não são recompilados na 1ª chamada após serem inválidados, eles são recompilados apenas na 2º chamada. Isso implica em erros nas aplicações na 1º chamada aos objetos inválidos. Ressalto que isso ocorre somente em objetos inválidos que dependem de objetos remotos (objetos não localizados na mesma instância de Banco de Dados, acessados por meio de Database Links).

  

 

3- Recompilando objetos inválidos

    Após alterar um ou mais objetos no Banco de Dados é uma boa prática recompilar todos os objetos inválidos no Banco de Dados para evitar que objetos com dependências remotas não gerem erros na 1º chamada e para agilizar a execução de objetos com depedências locais, em sua 1ª chamada.

     Se for necessário consultar depedências diretas de um objeto a ser alterado, consulte a visão USER_DEPENDENCIES:

           SELECT    name, type, referenced_name, referenced_type

           FROM       USER_DEPENDENCIES

          WHERE    referenced_name = ('&NOME_OBJETO');  -- informar nome do objeto

        

    Se for necessário verificar todas as depêndencias (diretas e indiretas) de um objeto é necessário executar previamente o script utldtree.sql, que encontra-se na pasta $ORACLE_HOME/rdbms/admin do servidor de Banco de Dados. Este script deverá ser executado uma única vez. Se você já executou o script utldtree.sql, execute a procedure deptree_fill, como no exemplo abaixo:

          BEGIN   

               deptree_fill('&tipo_objeto','&owner_objeto','&nome_objeto');  

          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; 

  

       Existem vários métodos para recompilar objetos inválidos. Vou mostrar neste artigo o método que eu considero mais fácil e mais eficiente, que é a recompilação de objetos inválidos através da procedure UTL_RECOMP.recomp_parallel. No exemplo a seguir, a procedure recebe 2 parâmetros. O primeiro indica o número de threads que o Banco de Dados utilizará para a recompilação e o segundo indica o nome de um schema que se deseja recompilar os seus objetos. Se o valor do segundo parâmetro for igual a NULL, os objetos de todos os schemas do Banco de Dados serão recompilados:

               BEGIN

                     UTL_RECOMP.recomp_parallel(4,NULL);

               END;

     Segue abaixo uma procedure que eu criei que eu sempre utilizo para verificar objetos inválidos antes e depois de qualquer alteração que eu faço em objetos do Banco de Dados. A procedure recompila todos os objetos de um Banco de Dados utilizando paralelismo, que permite uma recompilação mais rápida. A procedure permite enviar e-mail para verificar uma lista dos objetos inválidos antes e depois da recompilação. Para criar a stored procedure é necessário:
            1- Instalar a package UTL_MAIL, que não é instalada por default no Banco de Dados Oracle;
            2- Para enviar e-mails através da package UTL_MAIL é necessário configurar um valor indicando o nome de um servidor SMTP para o parâmetro de sistema do Banco de Dados SMTP_OUT_SERVER;
            3- Para enviar e-mails é necessário que a conta de envio criada no servidor SMTP não exija autenticação para envio de mensagens;
            4- Ter os seguintes privilégios:

                         - SELECT na visão V$INSTANCE;

                         - EXECUTE na package UTL_TCP;

                         - EXECUTE na package UTL_MAIL;

                         - EXECUTE na package UTL_RECOMP.

Código da stored procedure:
-------------------------------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE SP_COMPILAR_OBJETOS_INVALIDOS(         
          p_EnviarEmail IN NUMBER,
          p_ContaEmailOrigem IN VARCHAR2,
          p_ContaEmailDestino IN VARCHAR2)
    IS   
--------------------------------------------------------------------------------
--  PROCEDURE SP_Compilar_Objetos_Invalidos
--------------------------------------------------------------------------------
-- 26/05/2011 [Fábio Prado] -> Procedure utilizada para compilar todos os objetos inválidos de um Banco de Dados.
--   Se  valor de p_EnviarEmail = 1 envia e-mail para interessados informando lista de objetos inválidos antes e depois da recompilação
--------------------------------------------------------------------------------
-- PARÂMETROS
--------------------------------------------------------------------------------
-- p_EnviarEmail ->  valor 1 indica que deve ser enviado e-mail para a
--                  scbd, caso contrário, irá executar dbms_output
-- p_ContaEmailOrigem -> conta de email para enviar mensagem
-- p_ContaEmailDestino -> conta(s) de email para receber mensagem. Se for necessário enviar para mais de uma conta, separe os nomes pelo caractere vírgula
--------------------------------------------------------------------------------       
        rawMessage RAW(32767);
        strMessage VARCHAR2(4000);
        strSubject VARCHAR2(100);
        strInstance VARCHAR2(20);
        strDate CHAR(8);
        intContTotalAntes NUMBER:=0;
        intContTotalDepois NUMBER:=0;
        intCont NUMBER:=0;
    BEGIN
        IF p_EnviarEmail = 1 THEN
            -- recupera nome da instancia do BD
            SELECT UPPER(INSTANCE_NAME) INTO strInstance
            FROM    V$INSTANCE;
       
            -- recupera data atual
            select to_char(sysdate,'dd/mm/yy') into strDate from dual;
           
            -- CABECALHO DO CORPO DO ANEXO DO EMAIL
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('OBJECT TYPE   #   OWNER   #   OBJECT NAME   #   MESSAGE' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
        END IF;
       
        SELECT    COUNT(1) INTO intContTotalAntes
        FROM      all_objects
        WHERE     status != 'VALID';
               
        IF intContTotalAntes > 0 THEN
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('     *** OBJETOS INVÁLIDOS ANTES DA COMPILAÇÃO ***     '|| UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
             FOR cur_rec IN (SELECT     O.owner,
                                        O.object_type,
                                        O.object_name,
                                        O.object_id
                             FROM       all_objects O
                             WHERE      status != 'VALID')
                    LOOP
                        intCont:=intCont+1;
                       
                        IF p_EnviarEmail = 1 THEN               
                            rawMessage:= rawMessage || utl_raw.cast_to_raw(intCont || '- ' || cur_rec.object_type || ' : ' || cur_rec.owner ||
                                 ' : ' || cur_rec.object_name || ' -> INVALID' || UTL_TCP.CRLF);                       
                        ELSE
                            DBMS_OUTPUT.put_line(intCont || '- ' || cur_rec.object_type || ' : ' || cur_rec.owner || ' : ' || cur_rec.object_name || ' -> INVALID');
                        END IF;                   
                    END LOOP;       
        END IF;
           
        -- recompila todos os objetos invalidos utilizando paralelismo (4 threads)   
        EXECUTE IMMEDIATE 'BEGIN UTL_RECOMP.recomp_parallel(4,NULL); END;';
       
        SELECT    COUNT(1) INTO intContTotalDepois
        FROM      all_objects
        WHERE     status != 'VALID';
       
        intCont:=0;       
       
        -- verifica se ainda existem objetos inválidos
        IF intContTotalDepois > 0 THEN       
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('    *** OBJETOS INVÁLIDOS DEPOIS DA COMPILAÇÃO ***     '|| UTL_TCP.CRLF);
            rawMessage:= rawMessage || utl_raw.cast_to_raw('=======================================================' || UTL_TCP.CRLF);
           
                    FOR cur_rec IN (SELECT     O.owner,
                                                O.object_type,
                                                O.object_name,
                                                O.object_id
                                     FROM       all_objects O
                                     WHERE      status != 'VALID')
                    LOOP
                        intCont:=intCont+1;
                       
                        IF p_EnviarEmail = 1 THEN               
                            rawMessage:= rawMessage || utl_raw.cast_to_raw(intCont || '- ' || cur_rec.object_type || ' : ' || cur_rec.owner ||
                                 ' : ' || cur_rec.object_name || ' -> INVALID' || UTL_TCP.CRLF);                       
                        ELSE
                            DBMS_OUTPUT.put_line(intCont || '- ' || cur_rec.object_type || ' : ' || cur_rec.owner || ' : ' || cur_rec.object_name || ' -> INVALID');
                        END IF;                   
                    END LOOP;       
        END IF;
       
        IF p_EnviarEmail = 1 THEN               
                strSubject:='Compilação automática de objetos inválidos no BD ' || strInstance;
                strMessage:='Total de objetos inválidos ANTES do processo de compilação: ' || intContTotalAntes || UTL_TCP.CRLF;
                strMessage:=strMessage || 'Total de objetos inválidos DEPOIS do término do processo de compilação: ' || intContTotalDepois || UTL_TCP.CRLF;
                -- envia mensagem por e-mail
                UTL_MAIL.SEND_ATTACH_VARCHAR2(p_ContaEmailOrigem,p_ContaEmailDestino,null,null,strSubject,strMessage,null,null,utl_raw.CAST_TO_VARCHAR2(rawMessage),NULL,null,'obj_inv_' || strDate || '.txt');
        END IF;
    END;

-------------------------------------------------------------------------------------------------

     Bom... por hoje é só pessoal! Até o próximo artigo.

  

  

  

Referências:

      - Material oficial do treinamento Oracle: Oracle Database 10g: Develop PL/SQL Program Units
      - http://www.dba-oracle.com/t_tracking_table_constraint_dependencies.htm