- Este tópico contém 5 respostas, 2 vozes e foi atualizado pela última vez 9 anos, 10 meses atrás por
rman.
-
AutorPosts
-
23 de outubro de 2014 às 2:01 am #107045
C-S-R
ParticipanteSalve 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
Cesar23 de outubro de 2014 às 2:34 pm #107047rman
Participante@C-S-R
Como você está descobrindo as dependências?
Em relação aos registros órfãos, o banco têm integridade referencial?
23 de outubro de 2014 às 6:40 pm #107048C-S-R
ParticipanteOpa 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?
23 de outubro de 2014 às 10:54 pm #107049rman
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 ISFUNCTION 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. 🙂
4 de novembro de 2014 às 12:49 am #107086C-S-R
ParticipanteOpa 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.
4 de novembro de 2014 às 2:18 pm #107087rman
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?
-
AutorPosts
- Você deve fazer login para responder a este tópico.