Home oracle Oracle. Download using SQLLDR (Direct Path Load). Does rollback work in error?

Oracle. Download using SQLLDR (Direct Path Load). Does rollback work in error?




Wegor large volumes (about 500 thousand entries) every 5 minutes using SQLLDR into a partitioned table. Sections on the clock, plus there are still subsection (12 pieces).
Control File:

options (direct = true, errors = 500000)
Load Data.
Infile '{FileName}'
INTO Table TBL_Name.
Fields Terminated by '|'
Trailing Nullcols

Sometimes Deadlock occurs. At the same time found an incomprehensible problem. We expect that when an Oracle error occurs, you must execute Rollback of all loaded lines. But the data verification in the target table showed that the data did not roll back, but were loaded.

How can this be explained? At the end of the download in the log file, see:

sql * loader-925: Error While Uldlgs: Ocistmtexecute (PTC_HP)
ORA-00060: Deadlock Detected While Waiting For Resource
SQL * Loader-2026: The Load Was Abled Because SQL Loader Cannot Continue.
Table TBL_Name:
  0 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.
  Date Conversion Cache Disabled Due To Overflow (Default Size: 1000)

Answer 1, Authority 100%

no, rollback (rollback ), in the usual understanding of this term, with the option Direct = True SQL * Loader produces.

Direct Path Load Do not write none undo nor (default) Redo Logs .
Downloadable data is prepared in the buffer and are written directly to DataFile using non-formatted blocks over HWM (High-Water-Mark ).

If an error occurs (errors = 0) or, if the threshold of additional errors is exceeded (Errors & GT; 0), SQL * Loader interrupts the download, but all downloaded pages will be saved. Read more about the interruption of downloads in the INTERRUPTED LOADS .

Warning example for clarity. Let’s try to write null in the limit column not null :

create table stage (ID Number, Item Varchar (32), Created Date)
  Partition by Range (Created)
    Partition Stage_P1 Values ​​Less Than (Date'2018-06-21 ')

generate 100 data records with null in 66 row:

insert into stage
  Case WHEN ROWNUM & LT; & GT; 66 Then 'Item' || ROWNUM END, DATE'2018-06-20 '+ ROWNUM / 24
  from xmltable ('1 to 100');
$ sqlplus -l -s user / pass @ localhost / db1 & lt; & lt; eof
  SET COLSEP ';' Pages 0 Feedback Off Termout Off
  Spool Items.dat.
  Spool Off
   1; Item 1; 20180620t010000
   2; Item 2; 20180620T020000
  100; Item 100; 20180624T040000

Set a limit:

truncate table stage;

In the control file, set: interrupt the first error, page 30 entries:

options (direct = true, rows = 30, errors = 0)
Load Data.
Into Table Stage.
Fields Terminated by ';' (
  Item "Trim (: Item)",

Run the download:

$ sqlldr user / pass @ localhost / db1 control = items.ctl
Path Used: Direct 
Save data point reached - logical record count of 30.
Save data point reached - logical record count of 60.
Load completed - logical record count of 90.
Table STAGE:
 65 Rows successfully loaded.
Check the log file: items.log for more information about the load.

Notice, Save data point reached , and not Commit data point reached .

The log file can be seen that the first, second, and partially (up to the incorrect entry) third pages are preserved, and that SQL * Loader interrupted load:

Control File: items.ctl
Data File: items.dat
 Bad File: items.bad
 Discard File: none specified
 (Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 0
Continuation: none specified
Path used: Direct
Table STAGE, loaded from every logical record.
Insert option in effect for this table: TRUNCATE
  Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- - --- ---------------------
  SQL string for column: "trim (: item)"
Record 66: Rejected - Error on table STAGE.
ORA-00604: error occurred at recursive SQL level 1
ORA-01400: can not insert NULL into ( "DB" "STAGE" "ITEM"..)
Specify SKIP = 66 when continuing the load.
MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.
Table STAGE:
 65 Rows successfully loaded.
 1 Row 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.
 Date cache:
  Max Size: 1000
  Entries: 60
  Hits: 0
  Misses: 0
 Partition STAGE_P1: 23 Rows loaded.
 Partition SYS_P1116: 24 Rows loaded.
 Partition SYS_P1117: 18 Rows loaded.
Bind array size not used in direct path.
Column array rows: 30
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records skipped: 0
Total logical records rejected: 1
Total logical records discarded: 0
Total stream buffers loaded by SQL * Loader main thread: 3
Total stream buffers loaded by SQL * Loader load thread: 0
Run began on Thu Jun 21 22:27:38 2018
Run ended on Thu Jun 21 22:27:38 2018
Elapsed time was: 00: 00: 00.55
CPU time was: 00: 00: 00.05

Programmers, Start Your Engines!

Why spend time searching for the correct question and then entering your answer when you can find it in a second? That's what CompuTicket is all about! Here you'll find thousands of questions and answers from hundreds of computer languages.

Recent questions