Home > diagnostic > bulk commit

bulk commit

For bulk operations it’s helpfull to commit frequently to avoid ora-1555 (snapshot too old) and avoid long lockings.
with the following pl/sql-block you can handle this easier

DECLARE
 currentcursor NUMBER := 0;
 total NUMBER := 0;
CURSOR delete_current_record IS
 SELECT rowid FROM BIGEMP;
BEGIN
 FOR rec IN delete_current_record LOOP
 DELETE FROM BIGEMP
 WHERE rowid = rec.rowid;
 total := total + 1;
currentcursor := currentcursor + 1;
 IF (currentcursor >= 10000) THEN
 COMMIT;
 currentcursor := 0;
 END IF;
 END LOOP;
 COMMIT;
 END;
 /
Advertisements
Categories: diagnostic
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: