 |
|
Oracle SQL*Loader commit point tips
Oracle Database Tips by Donald BurlesonOctober 27, 2015
|
Question: Can I
control the commit frequency when doing a large data load with
SQL*Loader? Is there a sqlldr parameter that controls
commit points and limits for commits? Is it possible to
only commit at the end of a SQL*Loader job?
Answer: Yes, there is
the ROWS sqlldr parameter that controls the commit frequency.
For example, If you use the SQL*Loader parameter ROWS=1000, we
asking sql*loader to commit after every thousand rows are
loaded. You may also be able to try
ROWS=9999_high_values to get SQL*Loader to commit at the end
of the job, but beware of potential undo log (rollback segment)
issues, such as the
ORA-01555 Snapshot Too Old
error.
As to the default commit
frequency, we see this example by
Jonathan Gennick, indicating that the default for SQL*Loader
is to commit frequently, in this example about every 28 rows:
$ sqlldr gnis/gnis@donna control=gnis log=gnis_michigan
data=mi_deci.
SQL*Loader: Release 8.1.5.0.0 - Production on Wed Apr 5 13:35:53 2000
? Copyright 1999 Oracle Corporation. All rights reserved.
Commit point reached - logical record count 28
Commit point reached - logical record count 56
Commit point reached - logical record count 84
