Home » RDBMS Server » Server Utilities » Bulk insert with SQL*Loader fails to abort inserts on index errors (SQL*Loader)
icon4.gif  Bulk insert with SQL*Loader fails to abort inserts on index errors [message #669497] Thu, 26 April 2018 01:12 Go to next message
ivanskodje
Messages: 3
Registered: April 2018
Junior Member
Greetings.

Do you by chance know if it is possible to tell the SQL*Loader not to insert the data it detects index errors on?
Currently it returns an error code (2) and informs me that it succeeded inserting the aforementioned rows.


The error message is as followed (forgive me for the Swedish text):
The following index(es) on table VALUE_CODE were processed:
index DBONE.INDX_ONSERIALNUM gjordes oanvändbart pga.:
ORA-00001: brott mot unik begränsning (DBONE.INDX_ONSERIALNUM)
indexet DBONE.INDX_MEMBER_GETCOUNT ignorerades på grund av:
SKIP_UNUSABLE_INDEXES angavs och indexsegmentet var oanvändbart före laddningen
indexet DBONE.INDX_MEMBER_REPORT ignorerades på grund av:
SKIP_UNUSABLE_INDEXES angavs och indexsegmentet var oanvändbart före laddningen
index DBONE.INDX_MEMBER_SESSIONID gjordes oanvändbart pga.:
ORA-00001: brott mot unik begränsning (DBONE.INDX_MEMBER_SESSIONID)
indexet DBONE.INDX_CODEANDSTATUS ignorerades på grund av:
SKIP_UNUSABLE_INDEXES angavs och indexsegmentet var oanvändbart före laddningen
laddningen av index DBONE.PK_MEMBER lyckades med 1 nycklar

Table VALUE_CODE:
  1 Row 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.

The control file looks like this (without columns):
LOAD DATA INFILE 'files\bulk.201804251345' 
 APPEND PRESERVE BLANKS INTO TABLE MEMBER 
 fields terminated by "#" optionally enclosed by '|'  
 trailing nullcols 
 ( REMOVED )
Re: Bulk insert with SQL*Loader fails to abort inserts on index errors [message #669500 is a reply to message #669497] Thu, 26 April 2018 03:22 Go to previous messageGo to next message
ivanskodje
Messages: 3
Registered: April 2018
Junior Member
I'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?
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8916
Registered: November 2002
Location: California, USA
Senior Member
ivanskodje wrote on Thu, 26 April 2018 01:22
I'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
1
2
3
2
4

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


Re: Bulk insert with SQL*Loader fails to abort inserts on index errors [message #669594 is a reply to message #669585] Wed, 02 May 2018 01:09 Go to previous message
ivanskodje
Messages: 3
Registered: April 2018
Junior Member
Appreciate it, I will give it a go. My only worry is slowing it down, since there will be millions of rows.

Any inconsistencies with the snippets I've posted above would be to me changing the names to sensor original table names and such. Smile
Previous Topic: Sql Loader - oracle
Next Topic: ORA-01722
Goto Forum:
  


Current Time: Wed Aug 21 17:23:54 CDT 2019