Commit during loader replace ?

5 pts.
Oracle development
SQL Loader
Hi. I would like to know if sql*loader commits after it deletes the rows when executing loader option replace. Is it possible to make it commit only after everything is done, so that there is no second when the table is empty. Thanks for your help.

Answer Wiki

Thanks. We'll let you know when a new response is added.


I can’t answer that question directly and unable to test it at this time. However this information may assist you in your testing.

The REPLACE keyword says, “remove any existing rows before starting the load.”

You can’t stop a commit from occurring during SQL*Loader loading, but by setting the ROWS= parameter to a large value, committing can be reduced. Make sure you have big rollback segments ready when you use a high value for ROWS=.

I am wondering, if by setting ROWS= to a high number (depending on your system specifications along with the number of rows you are working with and size of rows) if this might work for you.

Please let us know if this helps you in your endeavors.



Discuss This Question: 2  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    I tested Rdano's approach (on Oracle 8i), but it doesn't work because a commit is issued after the initial deletion, and thus, delaying the next commits only makes the table remains empty for a longer period of time. After (next time I will do it before) running the test, I went to the documentation, and it says:
    Loading Data into Nonempty Tables
    If the tables you are loading into already contain data, you have three options: 
    •	APPEND 
    •	REPLACE 
    Caution: When the REPLACE or TRUNCATE keyword is specified, the entire table is replaced, not just individual rows. After the rows are successfully deleted, a commit is issued. You cannot recover the data that was in the table before the load, unless it was saved with Export or a comparable utility. 
    Good luck.
    85,025 pointsBadges:
  • Rdano
    Good find Carlosdl. I think the only option to ensure that the table is never empty would be to SQL Load into a different table and then run a PL/SQL procedure to do whatever, update the records delete all and insert all from the different table. Your options there depending on the scenario and setup.
    175 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: