这篇博客探讨了在Oracle 10g中使用SQLLoader进行直接路径和常规路径装载时遇到的问题，特别是涉及序列生成和主键约束。文章通过实例展示了如何处理直接路径装载时序列无法正确生成主键值的错误，以及如何解决违反唯一约束条件的问题。
SQL> SELECT * FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> DESC TEST;
名称 是否为空? 类型
----------------------------------------- --------
----------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DEPTNO NUMBER(3)
SQL> SELECT * FROM TEST;
未选定行
SQL> CREATE SEQUENCE S;
序列已创建。
SQL> HOST TYPE TEST.CTL
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME,SAL,DEPTNO,EMPNO "S.NEXTVAL")
BEGINDATA
ALLEN,1600,30
JONES,3123.75,20
MARTIN,1312.5,30
CHAN,3450,20
CLARK,2572.5,10
KING,5500,10
MILLER,920,10
SQL> HOST SQLLDR ADMIN/ADMIN
CONTROL=TEST.CTL
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 27
13:12:18 2010
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
加载完成 - 逻辑记录计数 7。
SQL> SELECT * FROM TEST;
未选定行
日志文件里有如下内容：
记录 1: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 2: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 3: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 4: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 5: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 6: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
记录 7: 被拒绝 - 表 TEST 出现错误。
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01400: 无法将 NULL 插入 ("ADMIN"."TEST"."EMPNO")
表 TEST:
0 行 加载成功。
由于数据错误, 7 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
由于直接路径装载会绕过SQL引擎，所以我们的序列并没有生成,从而导致为EMPNO列提供的值为空。而EMPNO是主键列，所以不允许为空。从而导致错误。
去掉表TEST的主键看看。
SQL> ALTER TABLE TEST DROP PRIMARY KEY;
表已更改。
SQL> DESC TEST;
名称 是否为空? 类型
----------------------------------------- --------
----------------------------
EMPNO NUMBER
ENAME VARCHAR2(20)
SAL NUMBER(7,2)
DEPTNO NUMBER(3)
SQL> HOST TYPE TEST.CTL
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME,SAL,DEPTNO,EMPNO "S.NEXTVAL")
BEGINDATA
ALLEN,1600,30
JONES,3123.75,20
MARTIN,1312.5,30
CHAN,3450,20
CLARK,2572.5,10
KING,5500,10
MILLER,920,10
SQL> HOST SQLLDR ADMIN/ADMIN
CONTROL=TEST.CTL
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 27
13:31:46 2010
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
加载完成 - 逻辑记录计数 7。
SQL> SELECT * FROM TEST;
EMPNO
ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
ALLEN 1600 30
JONES 3123.75 20
MARTIN 1312.5 30
CHAN 3450 20
CLARK 2572.5 10
KING 5500 10
MILLER 920 10
已选择7行。
可以看到去掉NOT
NULL约束就可以装载成功了，但是我们的EMPNO列却是空的。对于DIRECT=TRUE的SQLLDR装载，就不能用自定义序列。要想使用自定义序列，SQLLDR就得用常规路径方式装载。如果必须使用DIRECT=TRUE
带序列的方式装载数据，我们需要SQLLDR提供的SEQUENCE函数。
SQL> SELECT * FROM TEST;
EMPNO
ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1
ALLEN 1600 30
2
JONES 3123.75 20
3
MARTIN 1312.5 30
4
CHAN 3450 20
5
CLARK 2572.5 10
6
KING 5500 10
7
MILLER 920 10
已选择7行。
表里已经有7条数据。最大EMPNO是7。
SQL> ALTER TABLE TEST ADD PRIMARY
KEY(EMPNO);
表已更改。
SQL> HOST TYPE TEST.CTL
OPTIONS (DIRECT=TRUE)
UNRECOVERABLE
LOAD DATA
INFILE *
APPEND -- 这里用了APPEND方式
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME,SAL,DEPTNO,EMPNO SEQUENCE(MAX,1))
BEGINDATA
ALLEN,1600,30
JONES,3123.75,20
MARTIN,1312.5,30
CHAN,3450,20
CLARK,2572.5,10
KING,5500,10
MILLER,920,10
SQL> HOST SQLLDR ADMIN/ADMIN
CONTROL=TEST.CTL
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 27
13:55:01 2010
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
加载完成 - 逻辑记录计数 7。
SQL> SELECT * FROM TEST;
EMPNO
ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1
ALLEN 1600 30
2
JONES 3123.75 20
3
MARTIN 1312.5 30
4
CHAN 3450 20
5
CLARK 2572.5 10
6
KING 5500 10
7
MILLER 920 10
8
ALLEN 1600 30
9
JONES 3123.75 20
10
MARTIN 1312.5 30
11
CHAN 3450 20
EMPNO
ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
12
CLARK 2572.5 10
13
KING 5500 10
14
MILLER 920 10
已选择14行。
别忘记了表TEST用的SEQUENCE需要修改。否则下次会报唯一约束错误。
SQL> SELECT MAX(EMPNO) FROM TEST;
MAX(EMPNO)
----------
14
SQL> DROP SEQUENCE S;
序列已删除。
SQL> CREATE SEQUENCE S START WITH 1 INCREMENT
BY 1;
序列已创建。
最后我们要检查我们的表中是否存在唯一约束违反的情况。因为使用直接路径装载的时候，如果表的字段有主键或者唯一约束，并且装载的数据违反了唯一性约束，那么SQLLOADER将相关索引置为无效，继续装载。装载完毕后，索引不会自动置为有效，需要DBA的手工干预。
SQL> COL CONSTRAINT_NAME FORMAT A20
SQL> COL CONSTRAINT_TYPE FORMAT A20
SQL> COL INDEX_NAME FORMAT A20
SQL> COL INDEX_TYPE FORMAT A20
SQL> SELECT
CONSTRAINT_NAME,CONSTRAINT_TYPE,STATUS FROM USER_CONSTRAINTS WHERE
TABLE_NAME='TEST';
CONSTRAINT_NAME CONSTRAINT_TYPE STATUS
-------------------- -------------------- --------
SYS_C005400 P ENABLED
ENAME_UNIQUE U ENABLED
表TEST的ENAME列为唯一约束。
SQL> SELECT INDEX_NAME,INDEX_TYPE,STATUS FROM
USER_INDEXES WHERE TABLE_NAME='TEST';
INDEX_NAME INDEX_TYPE STATUS
-------------------- -------------------- --------
SYS_C005400 NORMAL VALID
ENAME_UNIQUE NORMAL UNUSABLE
TEST_DEPTNO_IDX NORMAL VALID
由于装载过程中ENAME列违反了唯一性约束，所以相关索引被置为无效。
以下是日志文件里的内容一部分内容：
表 TEST 的以下索引已处理:
索引 ADMIN.ENAME_UNIQUE 无法使用, 因为:
ORA-00001: 违反唯一约束条件 (ADMIN.ENAME_UNIQUE)
索引 ADMIN.SYS_C005400 已成功加载, 具有 7 个关键字
索引 ADMIN.TEST_DEPTNO_IDX 已成功加载, 具有 7 个关键字
表 TEST:
7 行 加载成功。
由于数据错误, 0 行 没有加载。
由于所有 WHEN 子句失败, 0 行 没有加载。
由于所有字段都为空的, 0 行 没有加载。
以下是一个应用常规路径带序列的装载例子
SQL> HOST TYPE TEST.CTL
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE TEST
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(ENAME,SAL,DEPTNO,EMPNO "S.NEXTVAL")
BEGINDATA
ALLEN,1600,30
JONES,3123.75,20
MARTIN,1312.5,30
CHAN,3450,20
CLARK,2572.5,10
KING,5500,10
MILLER,920,10
SQL> HOST SQLLDR ADMIN/ADMIN
CONTROL=TEST.CTL
SQL*Loader: Release 10.2.0.1.0 - Production on 星期三 1月 27
13:48:56 2010
Copyright (c) 1982, 2005, Oracle. All rights
reserved.
达到提交点 - 逻辑记录计数 7
SQL> SELECT * FROM TEST;
EMPNO
ENAME SAL DEPTNO
---------- -------------------- ---------- ----------
1
ALLEN 1600 30
2
JONES 3123.75 20
3
MARTIN 1312.5 30
4
CHAN 3450 20
5
CLARK 2572.5 10
6
KING 5500 10
7
MILLER 920 10
已选择7行。
437
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
