DBA Tips Archive for Oracle
How to perform long deletes - (commiting every n number of records)
by Jeff Hunter, Sr. Database Administrator
When performing a DELETE from a table, Oracle will generate rollback. If the particular table you are trying to delete from contains a large number of records, it is possible for the transaction to fail because of the rollback segment not being large enough. One way to solve this issue is to use the TRUNCATE command. TRUNCATE does not generate rollback and has the added benefit of rebuilding any indexes on the table. Another solution would be to use the anonymous PL/SQL block below.
DECLARE count NUMBER := 0; total NUMBER := 0; CURSOR del_record_cur IS SELECT rowid FROM <OWNER>.<TABLE_NAME> WHERE <YOUR_CRITERIA> BEGIN FOR rec IN del_record_cur LOOP DELETE FROM <OWNER>.<TABLE_NAME> WHERE rowid = rec.rowid; total := total + 1; count := count + 1; IF (count >= 1000) THEN COMMIT; count := 0; END IF; END LOOP; COMMIT; DBMS_OUTPUT.PUT_LINE('Deleted ' || total || ' records from <OWNER>.<TABLE_NAME>.'); END; /
Soon after publishing this months DBA Tip, Geurts Maarten wrote in to indicate that the above PL/SQL block may not work where you may have either many processes running on the system or if the table is too large. If you do not have a rollback segment large enough, you may encounter the "snapshot too old" error. This is because the code block is modifing the snapshot the first cursor uses to get its rowids from.
You need to reopen the snapshot.
A PL/SQL BLock like:
BEGIN loop Delete <OWNER>.<TABLE_NAME> where <YOUR_CRITERIA> and rownum < 5000 exit when SQL%rowcount < 4999 end loop; ENDwill do the trick just fine.
Basically a commit in a loop will fail after some time if a cursor stays open all that time, especially if the data in the cursor is modified.
About the above PL/SQLblock:
It keeps looping (executing the delete statement) until it deletes less than 4999 rows. So this is the last delete. if you do not add additional where statements the whole table is deleted.
Also note i use rownum < 5000. the following
BEGIN loop -- keep looping --do the delete 4999 in each iteration Delete <OWNER>.<TABLE_NAME> where <YOUR_CRITERIA> and rownum < 5000; -- exit the loop when there where no more 5000 reccods to delete. exit when SQL%rowcount < 4999; -- commit to clear the rollback segments. commit; -- this commit i forgot in the last mail, oops. end loop; commit; -- commit the last delete END;Note that the souce I typed is untested, it may have syntax errors. The point is: when you need big rollback statements it is getting slower. I have seen cases in a simular PL/SQL block (with an update instead of delete) where the first records were updated twice as fast as the last records. On the other hand, my constuction has to execute the SQL much more times, that is why i increased the commit size from 1000 to 5000.
Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.
All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at firstname.lastname@example.org.
I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.