15 May, 2012

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

3 comments:

  1. You are still ignoring the problem caused by keeping your cursor open.

    Unless you close/reopen the cursor, you still need the UNDO space to hold the cursor's initial state. This is required by Oracle's read-consistency mechanisms. If you do not have the UNDO space for a single statement, you are sure to run into an ORA-1555 error (snapshot too old).

    Also, you mention "overloading redo log file". Your code will produce a lot more REDO because you have to track the UNDO (redo and undo are not the same thing).

    ReplyDelete
    Replies
    1. i think for loop have implicit close cursor, so we don't need to close cursor explicit in for loop, am i right?

      Delete
  2. You ca use "FOR UPDATE" in cursor & current of clause in update statement then after one record update it automaically commit & also hole table not in lock state only one record at a time in lock state.




    Pushpendra Choubey

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...