|
|
|
|
| Re: Bulk insert with SQL*Loader fails to abort inserts on index errors [message #669585 is a reply to message #669500] |
Mon, 30 April 2018 18:26   |
 |
Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
ivanskodje wrote on Thu, 26 April 2018 01:22I've done some additional research since I posted this.
Quote:UNIQUE constraints are enforced both during and after the load. A record which violates a UNIQUE constraint is not rejected (the record is not available in memory when the constraint violation is detected.) source
Am I to understand that by using Direct Loads we cannot prevent data from being inserted when there is an constraint error?
Direct path loading handles different types of constraints in different ways. You cannot prevent duplicates from being inserted when UNIQUE constraints are violated. Your table names in your control file and error messages do not match. The following is a close approximation of what you have, based on your error messages. It looks like you have a unique index which is rendered unusable by the direct path load that allows duplicates. I never use direct path, because of all of its limitations. I recommend just using conventional path. I have provided a reproduction below in case someone else wants to experiment with it and make any suggestions.
-- data file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE bulk.201804251345
-- control file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.ctl
LOAD DATA INFILE 'bulk.201804251345'
APPEND PRESERVE BLANKS INTO TABLE value_code
fields terminated by "#" optionally enclosed by '|'
trailing nullcols
(serialnum)
-- table with unique index:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE value_code
2 (serialnum NUMBER)
3 /
Table created.
SCOTT@orcl_12.1.0.2.0> CREATE UNIQUE INDEX idx_onserialnum ON value_code (serialnum)
2 /
Index created.
SCOTT@orcl_12.1.0.2.0> COLUMN index_name FORMAT A30
SCOTT@orcl_12.1.0.2.0> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = 'VALUE_CODE'
4 /
INDEX_NAME STATUS
------------------------------ --------
IDX_ONSERIALNUM VALID
1 row selected.
-- load data using direct path, resulting in loading of duplicates and unusable index:
SCOTT@orcl_12.1.0.2.0> HOST SQLLDR scott/tiger control=test.ctl direct=true log=test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Apr 30 16:15:52 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Path used: Direct
Load completed - logical record count 5.
Table VALUE_CODE:
5 Rows successfully loaded.
Check the log file:
test.log
for more information about the load.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM value_code
2 /
SERIALNUM
----------
1
2
3
2
4
5 rows selected.
SCOTT@orcl_12.1.0.2.0> SELECT index_name, status
2 FROM user_indexes
3 WHERE table_name = 'VALUE_CODE'
4 /
INDEX_NAME STATUS
------------------------------ --------
IDX_ONSERIALNUM UNUSABLE
1 row selected.
-- SQL*Loader log file:
SCOTT@orcl_12.1.0.2.0> HOST TYPE test.log
SQL*Loader: Release 12.1.0.2.0 - Production on Mon Apr 30 16:15:52 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Control File: test.ctl
Data File: bulk.201804251345
Bad File: bulk.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Continuation: none specified
Path used: Direct
Table VALUE_CODE, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
SERIALNUM FIRST * # O(|) CHARACTER
The following index(es) on table VALUE_CODE were processed:
index SCOTT.IDX_ONSERIALNUM was made unusable due to:
ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found
Table VALUE_CODE:
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Bind array size not used in direct path.
Column array rows : 5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Total stream buffers loaded by SQL*Loader main thread: 1
Total stream buffers loaded by SQL*Loader load thread: 0
Run began on Mon Apr 30 16:15:52 2018
Run ended on Mon Apr 30 16:15:52 2018
Elapsed time was: 00:00:00.13
CPU time was: 00:00:00.04
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
