Pular para o conteúdo
Visualizando 6 posts - 1 até 6 (de 6 do total)
  • Autor
    Posts
  • #107045
    C-S-R
    Participante

      Salve caros colegas de profissão,

      Estou com um problema de loop infinito em um package.

      Na pkg eu passo 1 tabela que será expurgada e uma lista de tabelas que podem ser apagadas caso exista dependências e um período de retenção, não quero apagar todos os dados.
      Até ai blz funcionando bala.

      O Problema são dois.

      1º: Auto relacionamento

      Se a tabela A tiver um auto relacionamento, a pkg entra em um loop infinito.
      Pois ela verifica todas as tabelas que tem dependência da tabela A para poder apagar, nessa verificação ele retorna a Tabela A também, bingo loop infinito.

      2º: Registros órfãos.

      Se a tabela A estiver apontando para a tabela B, quando ela for expurgada os registros em B ficam órfãos.(Esse relacionamento é 1:1 pois se trata de um conteúdo de BLOB).
      Fiz uma modificação na pkg para ele retornar todas as tabelas que dependem da tabela A e as tabelas que a tabela A aponta, bingo loop infinito de novo.
      Pois quando ele retorna a tabela B ele faz novamente a verificação das dependências e a tabela A é uma delas.

      Resolvendo 1 loop infinito acredito que resolvo o outro.

      Alguém consegue me ajudar com esse problema, sem ter que mudar toda a lógica da package?

      Desde já agradeço.

      At
      Cesar

      #107047
      rman
      Participante

        @C-S-R

        Como você está descobrindo as dependências?

        Em relação aos registros órfãos, o banco têm integridade referencial?

        #107048
        C-S-R
        Participante

          Opa Rman, vlw pela ajuda

          Para achar as dependencias eu faço um select na all_constraints passando a PK na r_constraint_name.

          Como assim integridade referencial? vc quer dizer FK?

          #107049
          rman
          Participante

            @C-S-R

            Montei um cenário:


            CREATE TABLE PESSOAS(
            ID NUMBER
            ,NOME VARCHAR2(64)
            ,RESPONSAVEL_ID NUMBER
            )
            TABLESPACE TS_DELPHI_DATA;

            ALTER TABLE PESSOAS ADD CONSTRAINT PK_PESSOAS PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

            ALTER TABLE PESSOAS ADD CONSTRAINT FK_PESSOAS_PESSOAS FOREIGN KEY(RESPONSAVEL_ID) REFERENCES PESSOAS(ID);

            CREATE TABLE CARROS(
            ID NUMBER
            ,PESSOA_ID NUMBER
            ,PLACA VARCHAR2(16)
            )
            TABLESPACE TS_DELPHI_DATA;

            ALTER TABLE CARROS ADD CONSTRAINT PK_CARROS PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

            ALTER TABLE CARROS ADD CONSTRAINT FK_CARROS_PESSOAS FOREIGN KEY(PESSOA_ID) REFERENCES PESSOAS(ID);

            CREATE TABLE REVISOES(
            ID NUMBER
            ,CARRO_ID NUMBER
            ,QUILOMETRAGEM NUMBER(15,2)
            ,DATAREVISAO TIMESTAMP WITH TIME ZONE
            )
            TABLESPACE TS_DELPHI_DATA;

            ALTER TABLE REVISOES ADD CONSTRAINT PK_REVISOES PRIMARY KEY(ID) USING INDEX TABLESPACE TS_DELPHI_INDEX;

            ALTER TABLE REVISOES ADD CONSTRAINT FK_REVISOES_CARROS FOREIGN KEY(CARRO_ID) REFERENCES CARROS(ID);

            Consegui resolver o problema do auto relacionamento, teria que trabalhar mais caso os relacionamentos fechem o ciclo entre as entidades.


            CREATE OR REPLACE PACKAGE PKG_SQL IS

            FUNCTION TABLE_DEPENDENCIES(PTABLE IN VARCHAR2
                                                ,PLEVEL IN NUMBER DEFAULT 0) RETURN VARCHAR2;
            

            END PKG_SQL;
            /

            CREATE OR REPLACE PACKAGE BODY PKG_SQL IS

            FUNCTION TABLE_DEPENDENCIES(PTABLE IN VARCHAR2
                                                ,PLEVEL IN NUMBER DEFAULT 0) RETURN VARCHAR2 IS
                VCHIELD_TABLE VARCHAR2(32);
                VDEPENDENCIES NUMBER;
            BEGIN
                SELECT COUNT(*)
                INTO VDEPENDENCIES
                FROM USER_CONSTRAINTS P
                INNER JOIN USER_CONSTRAINTS R ON R.R_OWNER = P.OWNER
                                                  AND R.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
                                                  AND R.CONSTRAINT_TYPE = 'R'
                WHERE P.CONSTRAINT_TYPE = 'P'
                AND P.TABLE_NAME = PTABLE;
            
                IF VDEPENDENCIES > 0
                THEN
                    FOR VCURRENT_TABLE IN (SELECT R.TABLE_NAME
                                                  FROM USER_CONSTRAINTS P
                                                  INNER JOIN USER_CONSTRAINTS R ON R.R_OWNER = P.OWNER
                                                                                     AND R.R_CONSTRAINT_NAME = P.CONSTRAINT_NAME
                                                                                     AND R.CONSTRAINT_TYPE = 'R'
                                                  WHERE P.CONSTRAINT_TYPE = 'P'
                                                  AND P.TABLE_NAME = PTABLE)
                    LOOP
                        IF PTABLE = VCURRENT_TABLE.TABLE_NAME
                        THEN
                            DBMS_OUTPUT.PUT_LINE('AUTO RELATIONSHIP: ' || PTABLE);
                        ELSE
                            VCHIELD_TABLE := TABLE_DEPENDENCIES(PTABLE => VCURRENT_TABLE.TABLE_NAME
                                                                          ,PLEVEL => PLEVEL + 1);
                        END IF;
                    END LOOP;
                ELSE
                    IF PLEVEL = 0
                    THEN
                        DBMS_OUTPUT.PUT_LINE('NO DEPENDENCIES FOR: ' || PTABLE);
                    ELSE
                        DBMS_OUTPUT.PUT_LINE('LEAF: ' || PTABLE);
                    END IF;
            
                    RETURN PTABLE;
                END IF;
            
                IF PLEVEL = 0
                THEN
                    DBMS_OUTPUT.PUT_LINE('ROOT: ' || PTABLE);
                ELSE
                    DBMS_OUTPUT.PUT_LINE('LEVEL ' || PLEVEL || ': ' || PTABLE);
                END IF;
            
                RETURN VCHIELD_TABLE;
            END;
            

            END PKG_SQL;
            /

            Testando:


            DECLARE
            VLEAF_TABLE VARCHAR2(32);
            BEGIN
            VLEAF_TABLE := PKG_SQL.TABLE_DEPENDENCIES(PTABLE => 'PESSOAS');
            END;
            /

            Output:


            AUTO RELATIONSHIP: PESSOAS
            LEAF: REVISOES
            LEVEL 1: CARROS
            ROOT: PESSOAS

            Na definição da FK existe 3 tratamento para o DELETE:

            ON DELETE NO ACTION: Apresenta um erro ORA caso o registro possua dependências.
            ON DELETE CASCADE: Remove o registro e suas dependências.
            ON DELETE SET NULL: Remove o registro e atualiza as dependências para NULL. Essa é a forma que pode acontecer o registro órfão.

            Verifique qual é a melhor opção para você. Eu costumo trabalhar com ON DELETE NO ACTION.

            Se você optar por utilizar o ON DELETE CASCADE, analise muito bem, por que ele remove todas as dependências. Bom mas me parece que é isso mesmo que você quer, desta forma você não precisa implementar essa rotina que faria isso. 🙂

            #107086
            C-S-R
            Participante

              Opa Rman, obrigado pela ajuda e desculpe pela demora.

              Legal, consegui colocar a validação para auto-relacionamento.
              Caso acontece ele emite um erro e sai.

              Ainda estou com problemas nos relacionamentos 1-1. Inclui uma parte para verificar os relacionamentos nos dois sentidos. Mas estou prevendo muitos problemas, acho mais seguro mudar o relacionamento.

              Para deixar mais visual o problema é o seguinte.

              Tabela Log (id_log number, dt_log date, id_log_content number)
              Tabela Log_content (id_log_content number, content clob)

              Se eu apagar o log os log_content ficam órfãos. Acho que a solução mais segura é colocar o id_log na tabela log_content.

              No caso do DELETE CASCADE, tenho dois problemas.
              1 – O delete é extremamente demorado.
              Nessa package estamos copiando os dados que serão mantidos com um CTAS, dando um truncate e copiando de volta com insert select. De horas(delete) passou para minutos.
              2 – Como vc falou tem o problema de não saber oq estou apagando.

              vlw pela ajuda.

              #107087
              rman
              Participante

                @C-S-R

                Agora eu entendi sobre ficar orfão, mas na verdade você estaria removendo o filho e ficando o pai. Se você mudar o relacionamento colocando o id_log na tabela log_content a FK não irá permitir o filho sem pai.

                Todo relacionamento 1:1 pode ser eliminado, tornando apenas 1 tabela. Qual a razão de manter uma tabela separada para o armazenamento de um campo CLOB?

              Visualizando 6 posts - 1 até 6 (de 6 do total)
              • Você deve fazer login para responder a este tópico.
              plugins premium WordPress