Batched update

If you UNDO is not big enough or you want to split transaction here is the simple script for batched update

-- Execute batched update 
-- UPDATE table_name SET set_parameter;
--
-- USAGE
-- 1. Set l_batch_size
-- 2. Set select WHERE clause
-- 3. Set update's SET clause
-- 4. invoke process_table(table_name) for which this update statement should be executed
DECLARE 
  TYPE tpt_rowid IS TABLE OF ROWID;
 
  --1.Batch size
  g_bacth_size NUMBER := 100000;
  
  --2. SELECT rowid FROM table_name t WHERE ...value of variable below...
  l_select_where_clause VARCHAR2(32000) := q'[column_a = 111]';
  
  --3. UPDATE table_name SET ...value of variable below... WHERE rowid=rid
  l_update_set_clause VARCHAR2(32000) := q'[column_a = 333]';
  
  PROCEDURE process_table(p_table_name VARCHAR2) IS

    l_rids tpt_rowid;
    cur SYS_REFCURSOR;
  BEGIN
    OPEN cur FOR 'SELECT /*+ PARALLEL(8)*/rowid FROM ' || p_table_name || ' t WHERE ' || l_select_where_clause;
    LOOP
      
      FETCH cur BULK COLLECT INTO l_rids LIMIT g_bacth_size;
      dbms_output.put_line('Fetched: ' || l_rids.count);
      EXIT WHEN l_rids.count = 0;
      
      FORALL i IN l_rids.FIRST .. l_rids.LAST
        EXECUTE IMMEDIATE 
          'UPDATE ' || p_table_name || ' t' ||
          ' SET ' || l_update_set_clause || 
          ' WHERE rowid = :1' 
          USING l_rids(i);
      dbms_output.put_line('Updated: ' || SQL%ROWCOUNT); 
      COMMIT;
    END LOOP;
  END;
BEGIN
  --4. Invoke for all tables
  process_table('BB');  
END;
/