Home > IT Answers > Oracle > Commit during loader replace ?
Cgfchat
5 pts.
 Commit during loader replace ?
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.

Software/Hardware used:
ASKED: August 29, 2008  9:50 AM
UPDATED: September 9, 2008  5:26 PM
RELATED QUESTIONS
Answer Wiki:
Cgfchat, 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. Regards, Rdano
Last Wiki Answer Submitted:  September 3, 2008  3:27 pm  by  Rdano   175 pts.
All Answer Wiki Contributors:  Rdano   175 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

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 
•	TRUNCATE 
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.

carlosdl
 63,535 pts.
 

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.

Rdano
 175 pts.