http://www.dba-oracle.com/t_sql_loader_commit_frequency.htm
-
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
