Oracle sqlldr：在完成批加载后，也不会重新启用约束

我在Oracle数据库（19 c）中有以下表：

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);

它有一个主键和一个指向另一个表的外键。我想用sqlldr从一个csv文件导入数据到这个表（债务人）中。下面是 ctl 和 *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

完成任务后，日志文件将显示以下内容：
参考完整性约束/触发信息：NULL、UNIQUE和PRIMARY KEY约束不受影响。
在加载之前，约束mychema.DEBTORS.DEBTORS_FK0已禁用且未验证。处理了表mychema.DEBTORS上的以下索引：index mychema.DEBTORS_PK成功加载，有896个键
表mychema.DEBTORS没有约束异常表。加载后未重新启用任何约束、REFERENTIAL约束。
并且外键（debtors_fk0）变为禁用。正如你在 *ctl * 文件中看到的，我有REENABLE子句，但它似乎不工作。你能帮我弄明白问题出在哪里吗？我想让它自动重新启用约束
我创建了一个EXCEPT_TABLE表来存储所有异常，但它没有帮助

一切都在预料之中。如果你检查约束状态，你会看到它是ENABLED，但是NOT VALIDATED-如果有违反外键约束的行，它就不可能。

示例1：一切都好的时候会发生什么

示例表：

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.

两个类别（22，33）-它们都将在加载数据时使用，这意味着不会违反外键约束：

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>

控制文件：

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

加载会话：使用直接路径时，Oracle会自动禁用文档中所述的约束。

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>

日志文件说：

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

测试结果：

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>

示例2：缺少外键约束的父键。

示例表：

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.

类别现在缺少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>

控制文件未修改-样本数据仍包含ID_CAT = 33行。

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

调用sqlldr的方式没有任何变化;两行（即使是无效行！）加载：

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>

日志上写着（仔细阅读**！）：

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

结果：test（目标）表中的两行。except_Table现在包含违反外键约束的行。约束状态为启用未验证。

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>

如果你尝试手动验证外键约束，你将无法做到这一点：

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>

首先删除违反约束的行，然后验证它：

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>

因此，是的-一切都很好，并按预期工作。

Oracle sqlldr：在完成批加载后，也不会重新启用约束

1条答案

相关问题

热门标签

最新问答