主页 / coding / 问题 / 76936459
Accepted
bernie
Asked: 2023-08-20 02:09:53 +0800 CST

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

  • 772

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

它有另一个表的主键和外键。我想使用 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 上的以下索引已处理：索引 mychema.DEBTORS_PK 已成功加载 896 个键

表 mychema.DEBTORS 没有约束异常表。加载后没有重新启用 CHECK、REFERENTIAL 约束。

并且外键 (debtors_fk0) 被禁用。正如您在 *ctl *file 中看到的，我有 REENABLE 子句，但它似乎不起作用。您能帮我了解问题所在吗？我希望它自动重新启用约束

我创建了一个 EXCEPT_TABLE 表来存储所有异常，但这没有帮助

oracle

1 个回答

  1. Best Answer

    一切都如预期。如果检查约束状态，您会看到它已启用，但未验证- 如果存在违反外键约束的行，则不可能是启用状态。

    示例 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现在包含违反外键约束的行。约束状态为 ENABLED NOT VALIDATED。

    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>

    因此，是的 - 一切正常并且按预期工作。

    • 0

相关问题