Re: Running out of rollback

From: Andrew McAllister <mcallister_at_grad.missouri.edu>
Date: Thu, 16 Mar 1995 14:11:04
Message-ID: <mcallister.37.000E2FAE_at_grad.missouri.edu>

>In article <3k0775$20d_at_camelot.qdot.qld.gov.au> jb2_at_qdot.qld.gov.au (John
Blackburn) writes:

>>In my experience, deleting rows causes rollback activity for the simple
>>reason that you might need to rollback your transaction thus 'undeleting'
>>all your rows that you just deleted.
 

>>We had a problem similar to yours on our system. We overcame the problem
>>by having nested cursors that deleted a sufficiently small number of rows
>>at a time so as not to blow out the roll back segment, and performing
>>a commit between each group of rows deleted.
 

>>I hope this helps,
 

>This does but could you show an example. I'm not real big on SQL yet.
 

>Rex Warren

CREATE OR REPLACE PROCEDURE delete_from_table AS x NUMBER;
BEGIN
x := 0;
select count(*) into x from table_name;
WHILE x > 0 LOOP

     DELETE FROM table_name WHERE ROWNUM < 501;
     COMMIT;
     x := x - 500;

END LOOP;
COMMIT;
END; This will create a stored procedure. To call it, run this script to create the procedure. Then in SQLPLUS just type "execute delete_from_table;" You put the name of the table you want to delete from where it says "table_name" in the code above.
I use these for large tables that are too much of a pain to simply drop them.

Andy
Andrew McAllister -- mcallister_at_grad.missouri.edu Office of Research, University of Missouri-Columbia The views above are my own, and NOT those of my employer. Received on Thu Mar 16 1995 - 14:11:04 CET