Menu de navegação

[C-S-RParticipante]

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

[rmanParticipante]

@C-S-R

Como você está descobrindo as dependências?

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

[C-S-RParticipante]

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?

[rmanParticipante]

@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. 🙂

[C-S-RParticipante]

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.

[rmanParticipante]

@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?

[Autor]

Posts