Social Icons

26 de mai. de 2011

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

4 comments:

  1. Boa noite Fabio,

    Cara você por acaso ja criou alguma rotina que desfaz o WRAP feito pelo Oracle? Quando aprendi a usar esse utilitário de mascaramento de código no Oracle me disseram que não havia reversão
    para o código orignal, porém pesquisando um pouco na net tem algumas rotinas e tal, porém não consegui fazer funcionar.
    Voce tem algum rotina que faça isso com sucesso?

    ResponderExcluir
    Respostas
    1. Anônimo, eu nunca desenvolvi nada, mas veja o link http://www.codecrete.net/UnwrapIt

      []s

      Excluir
  2. Amigos, boa tarde.

    Pesquisei em vários lugares, porém, não encontrei sinais de solução.

    Necessidade: Passar nome da tabela na entrada da FUNÇÃO e COMPILAR

    Possuo uma PROC que lê um arquivo e insere o dados em uma devida tabela, porém essa tabela é criada quando se inicia a PROC, ou seja, a tabela não tem um nome fixo, porque eu utilizo a data e hora para diferenciar o arquivo.
    ex.: FILE_20170123170001

    SELECT TO_CHAR(SYSDATE, 'DDMMYYYHHMMSS') INTO V_NM_TABLE FROM DUAL;

    V_NM_TABLE := 'FILE_'||V_NM_TABLE ;

    Posteriormente a inserção desses registros, eu devo manipular alguns dados e para isso utilizo uma função na qual preciso passar o nome da tabela que que criei, para que ela possa fazer o select e fins devidos.

    A função está criada e funciona quando a tabela EXISTE no BD, segue função abaixo:

    CREATE OR REPLACE FUNCTION NOME_FUNCAO (CPF IN VARCHAR2, NM_TABELA IN VARCHAR2)
    RETURN VARCHAR2
    ...

    BEGIN
    ...
    For rCli in (

    Select TELEFONE,CPFCGC from NM_TABELA where CPFCGC = documento and ROWNUM <= 30

    )
    LOOP
    ...
    END LOOP;

    ...
    END;

    Existe algum jeito de validar a função para isso?

    Eu tentei fazer o seguinte: Criei a tabela, e testei a função, td certo! Depois apaguei a tabela e inseri a variavel, ai falho! então como não tinha nada a perder, criei a tabela com o nome da função, deu certo! compilei a função, deu certo! ai apaguei a tabela e tentei usar a função, rsrs... deu erro dizendo que a função está inválida. Enfim, não sei mais o que fazer.

    Se alguém puder me ajuda, desde já, obrigado.

    ResponderExcluir
    Respostas
    1. Em Oracle, e acredito que na maioria dos SGBDs, para uma procedure ou função estar válida o objeto tem que existir, certo? Sim, quando você apaga o objeto, a função fica inválida porque o objeto não existe, então a solução para o seu caso é unicamente escrever SQL dinâmico referenciando a tabela ou objeto que ainda não existe no momento da compilação da função.

      Para aprender a escrever sql dinâmico leia o artigo http://www.fabioprado.net/2015/06/executando-sql-dinamico-no-oracle.html.

      []s

      Excluir

 

LINKS ÚTEIS

Total de visualizações de página

Seguidores

Meu One Drive (antigo Sky Drive)