Início / coding / Perguntas / 76936459
Accepted
bernie
Asked: 2023-08-20 02:09:53 +0800 CST

Oracle sqlldr: as restrições não são reativadas após o término do carregamento do lote

  • 772

Tenho a seguinte tabela em meu banco de dados Oracle (19c):

CREATE TABLE debtors (
    bankruptID NUMBER NOT NULL,
    category VARCHAR2(50) NOT NULL,
    lastname VARCHAR2(100),
    firstname VARCHAR2(80),
    birthdate DATE,
    birthplace VARCHAR2(100),
    constraint DEBTORS_PK PRIMARY KEY (bankruptID));
    
ALTER TABLE debtors ADD CONSTRAINT debtors_fk0 FOREIGN KEY (category) REFERENCES categories(BankruptCategory);

Possui uma chave primária e uma chave estrangeira para outra tabela. Para esta tabela (devedores), desejo importar dados de um arquivo csv usando sqlldr. Aqui estão os arquivos ctl e *par *:

OPTIONS (SKIP=1)
LOAD DATA
CHARACTERSET CL8MSWIN1251
INTO TABLE myschema.debtors
REENABLE DISABLED_CONSTRAINTS EXCEPTIONS EXCEPT_TABLE
FIELDS TERMINATED BY '^'
TRAILING NULLCOLS
(
bankruptID,
category,
lastname,
firstname,
birthdate date 'YYYY-MM-DD HH24:MI:SS',
birthplace
)

userid=username/password@mydb
control=debtors.ctl
log=debtors.log
bad=debtors.bad
data=debtors.csv
direct=true

Depois de concluir a tarefa, o arquivo de log informa o seguinte:

Restrição de integridade referencial/informações de gatilho: as restrições NULL, UNIQUE e PRIMARY KEY não são afetadas.

A restrição mychema.DEBTORS.DEBTORS_FK0 foi desativada e atualizada antes do carregamento. O(s) seguinte(s) índice(s) na tabela mychema.DEBTORS foram processados: index mychema.DEBTORS_PK carregado com sucesso com 896 chaves

A tabela mychema.DEBTORS não possui tabela de exceção de restrição. Nenhuma restrição CHECK, REFERENTIAL foi reativada após o carregamento.

E a chave estrangeira (debtors_fk0) fica desativada. Como você pode ver no arquivo *ctl *, tenho a cláusula REENABLE, mas parece não estar funcionando. Você poderia me ajudar a entender qual é o problema? Eu quero que ele reative a restrição automaticamente

Criei uma tabela EXCEPT_TABLE para armazenar todas as exceções, mas não adiantou

oracle

1 respostas

  1. Best Answer

    Tudo é como esperado. Se você verificar o status da restrição, verá que está ATIVADO, mas NÃO VALIDADO - não pode ser se houver linhas que violam a restrição de chave estrangeira.

    Exemplo 1 : o que acontece quando tudo está bem?

    Tabelas de amostra:

    SQL> create table except_table
  2    (row_id     rowid,
  3     owner      varchar2(128),
  4     table_name varchar2(128),
  5     constraint varchar2(128)
  6    );

Table created.

    Duas categorias (22, 33) - AMBAS serão usadas durante o carregamento de dados, o que significa que a restrição de chave estrangeira não será violada:

    SQL> create table category
  2    (id_cat number primary key);

Table created.

SQL> insert into category values (22);

1 row created.

SQL> insert into category values (33);

1 row created.

SQL> create table test
  2    (id_test     number constraint pk_t primary key,
  3     id_cat      number constraint fk_tc references category,
  4     debit       number
  5    );

Table created.

SQL>

    Arquivo de controle:

    load data
infile *
replace
into table test
reenable disabled_constraints exceptions except_table
fields terminated by '|'
trailing nullcols
( id_test,
  id_cat,
  debit
)

begindata
1|22|456
2|33|777

    Carregando sessão: com caminho direto, o Oracle desabilita automaticamente as restrições conforme descrito na documentação.

    SQL>  $sqlldr scott/tiger@pdb1 control=test15.ctl log=test15.log direct=true

SQL*Loader: Release 21.0.0.0.0 - Production on Sat Aug 19 21:38:05 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 2.

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test15.log
for more information about the load.

SQL>

    Arquivo de log diz:

    Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint TEST.FK_TC was disabled and novalidated before the load.
The following index(es) on table TEST were processed:
index SCOTT.PK_T loaded successfully with 2 keys
TEST.FK_TC was re-enabled.

Table TEST has constraint exception table EXCEPT_TABLE.
Constraint TEST.FK_TC was validated

    Resultado:

    SQL> select * from test;

   ID_TEST     ID_CAT      DEBIT
---------- ---------- ----------
         1         22        456
         2         33        777

SQL> select * From except_table;

no rows selected

SQL> select constraint_type, table_name, status, validated
  2  From user_Constraints
  3  where constraint_name = 'FK_TC';

C TABLE_NAME      STATUS   VALIDATED
- --------------- -------- -------------
R TEST            ENABLED  VALIDATED        --> as everything went OK, constraint
                                                is enabled and validated
  
SQL>

    Exemplo #2 : chave pai da restrição de chave estrangeira ausente.

    Tabelas de amostra:

    SQL> drop table except_table;

Table dropped.

SQL> drop table test;

Table dropped.

SQL> drop table category;

Table dropped.

SQL> create table except_table
  2    (row_id     rowid,
  3     owner      varchar2(128),
  4     table_name varchar2(128),
  5     constraint varchar2(128)
  6    );

Table created.

    Categoria agora está faltando ID_CAT = 33:

    SQL> create table category
  2    (id_cat number primary key);

Table created.

SQL> insert into category values (22);

1 row created.

SQL> create table test
  2    (id_test     number constraint pk_t primary key,
  3     id_cat      number constraint fk_tc references category,
  4     debit       number
  5    );

Table created.

SQL>

    O arquivo de controle não foi modificado - os dados de amostra ainda contêm ID_CAT = 33 linhas.

    load data
infile *
replace
into table test
reenable disabled_constraints exceptions except_table
fields terminated by '|'
trailing nullcols
( id_test,
  id_cat,
  debit
)

begindata
1|22|456
2|33|777

    Nada mudou na maneira como o sqlldr está sendo chamado; ambas as linhas (mesmo a inválida!) são carregadas:

    SQL>  $sqlldr scott/tiger@pdb1 control=test15.ctl log=test15.log direct=true

SQL*Loader: Release 21.0.0.0.0 - Production on Sat Aug 19 21:44:00 2023
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle and/or its affiliates.  All rights reserved.

Path used:      Direct

Load completed - logical record count 2.

Table TEST:
  2 Rows successfully loaded.

Check the log file:
  test15.log
for more information about the load.

SQL>

    Log diz isso (leia com atenção !):

    Referential Integrity Constraint/Trigger Information:
NULL, UNIQUE, and PRIMARY KEY constraints are unaffected.

Constraint TEST.FK_TC was disabled and novalidated before the load.
The following index(es) on table TEST were processed:
index SCOTT.PK_T loaded successfully with 2 keys
TEST.FK_TC was re-enabled.

Table TEST has constraint exception table EXCEPT_TABLE.
TEST.FK_TC was not re-validated due to ORACLE error.
ORA-02298: cannot validate (SCOTT.FK_TC) - parent keys not found

    Resultado: ambas as linhas na testtabela (destino). except_Tableagora contém a linha que violou a restrição de chave estrangeira. O status da restrição é HABILITADO NÃO VALIDADO .

    SQL> select * from test;

   ID_TEST     ID_CAT      DEBIT
---------- ---------- ----------
         1         22        456
         2         33        777

SQL> select * From except_table;

ROW_ID             OWNER      TABLE_NAME      CONSTRAINT
------------------ ---------- --------------- ---------------
AAAZRjAAMAAAASbAAB SCOTT      TEST            FK_TC

SQL> select constraint_type, table_name, status, validated
  2  From user_Constraints
  3  where constraint_name = 'FK_TC';

C TABLE_NAME      STATUS   VALIDATED
- --------------- -------- -------------
R TEST            ENABLED  NOT VALIDATED

SQL>

    Se você tentar validar manualmente a restrição de chave estrangeira, não poderá fazer isso:

    SQL> alter table test modify constraint fk_tc enable validate;
alter table test modify constraint fk_tc enable validate
                                   *
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.FK_TC) - parent keys not found


SQL>

    Primeiro remova a(s) linha(s) que violaram a restrição e, em seguida, valide-a:

    SQL> delete from test
  2  where not exists (select null from category
  3                    where category.id_cat = test.id_cat);

1 row deleted.

SQL> alter table test modify constraint fk_tc enable validate;

Table altered.

SQL>

    Portanto, sim - está tudo bem e funciona conforme o esperado.

    • 0

relate perguntas