The selection of a subset of records from a very large set of records in Oracle ends

Question

I have a process that converts dates from GMT to Australian Eastern Standard Time. To do this, I need to select the records from the database, process them, and then save them back.

To select the entries, I have the following query:

SELECT id,
  user_id,
  event_date,
  event,
  resource_id,
  resource_name
FROM
  (SELECT rowid id,
    rownum r,
    user_id,
    event_date,
    event,
    resource_id,
    resource_name
  FROM user_activity
  ORDER BY rowid)
WHERE r BETWEEN 0 AND 50000

to select a block of 50,000 rows from approx. 60 million lines. I split them because a) Java (what is written during the upgrade) runs out of memory with too many lines (I have a bean for each line), and b) I have only 4 GB of Oracle space temp play with.

rowid ( ) rownum . , 50000 , ( java ).

, , , Oracle temp . , , .

( , ) , .

/ , /? , ( java-) , ?

.

pl/sql, :

declare
  cursor c is select event_date from user_activity for update;
begin
  for t_row in c loop
    update user_activity
      set event_date = t_row.event_date + 10/24 where current of c;
    commit;
  end loop;
end;

. , , . ?

Answer 1

, , , , . - . , temp, UNDO - . ( ALL_SEGMENTS, ?)

, , . 1200 :

where ora_hash(rowid, 1200) = 1
where ora_hash(rowid, 1200) = 2
...

, . , ? SQL - , , .

Answer 2

? pl/sql

declare
  cursor c is select * from aa for update;
begin
  for t_row in c loop
    update aa
     set val=t_row.val||' new value';
  end loop;
  commit;
end;

Answer 3

How about not updating it at all?

rename user_activity to user_activity_gmt

create view user_activity as
select id,
  user_id,
  event_date+10/24 as event_date,
  event,
  resource_id,
  resource_name
from user_activity_gmt;