I'm newbie with pl/sql and i need to do a large update in my database. More than 4 million entries must be changed and i want to execute a commit after every 5.000 update. I'm pretty lost to do this.
Here my query.
update accounts a set a.validateid = 'TH20381', flagexport = 25, exportname ='zde'
where a.accountnumber >= 35026879 and a.ownerid like 'V35%';
Thanks in advance.
If you really need to do that, you can consider using DBMS_PARALLEL_EXECUTE
package. Here is an example how it would look like:
DECLARE
v_sql VARCHAR2(4000);
BEGIN
-- create the task
DBMS_PARALLEL_EXECUTE.create_task (task_name => 'update_accounts_task');
-- define how the task should be split
DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name => 'update_accounts_task',
table_owner => 'YOUR_USERNAME',
table_name => 'ACCOUNTS',
by_row => true,
chunk_size => 5000);
-- command to be split and executed - notice the condition on rowid
-- which is required since we defined above that the task should be split
-- by rowid
v_sql := 'UPDATE accounts
SET validateid = ''TH20381'',
flagexport = 25,
exportname = ''zde''
WHERE accountnumber >= 35026879
AND ownerid LIKE ''V35%''
AND rowid BETWEEN :start_id AND :end_id';
-- run the task
DBMS_PARALLEL_EXECUTE.run_task(task_name => 'update_accounts_task',
sql_stmt => v_sql,
language_flag => DBMS_SQL.NATIVE,
parallel_level => 10);
END;
The user creating the task must be granted the CREATE JOB
privilege.
Based on article by Tim Hall accessible here: DBMS_PARALLEL_EXECUTE at Oracle Base