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

 

As to the default commit frequency, we see this example by , 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