Commit After n Updates
If you have table has a millions of rows and you want to update whole rows in table like below statement
It will raise an exception because of limited size of UNDO/ROLLBACK log file.
ORA-1555 errors, contact your DBA to increase the undo/ rollback segments.
To solve this problem by code, you can commit after n updates to ignore overloading redo log file.
In below script I will create cursor against my table TABLE_NAME and iterate through cursor and commit after updating every 1000 records.
You can change 1000 number to whatever number.
DECLARE LN$COUNTER NUMBER := 0; CURSOR LCUR$TABLE_CUR IS SELECT * FROM TABLE_NAME ; BEGIN FOR LREC$TABLE_REC IN LCUR$TABLE_CUR LOOP UPDATE
SET COLUMN_NAME = 'XXXXXXXXX' WHERE PK_COLUMNS=
; LN$COUNTER := LN$COUNTER + 1; IF MOD (LN$COUNTER, 1000) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; END;
Mahmoud A. El-Sayed