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
UPDATE TABLE_NAME
SET COLUMN_NAME='XXXXXX';
COMMIT;

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 TABLE_NAME
      SET COLUMN_NAME = 'XXXXXXXXX'  
     WHERE PK_COLUMNS=LREC$TABLE_REC.PK_COLUMNS;  
    LN$COUNTER := LN$COUNTER + 1;  
    IF MOD (LN$COUNTER, 1000) = 0  
    THEN  
      COMMIT;  
    END IF;  
   END LOOP;  
   COMMIT;  
 END;  


Thanks
Mahmoud A. El-Sayed

Popular posts from this blog

ADF : Get Current Logged User Name

OAF : Get Current Row in Table

ADF : Working with ViewCriteria