How to update/delete millions of rows from a big table safely?

Step1. Create a temporary table to save the ROWID and needed column s

SQL> create table dml_by_rowid as select ROWIDrid from orders where status=1;

OR using temporary table

SQL> create global temporary table dml_by_rowid(rid urowid) on commit preserve rows;
SQL> insert into dml_by_rowid as select rowid from orders where status=1;
SQL> commit;

Step2. Using PL/SQL to update/delete them

 set serveroutput on size unlimited
  declare
     v_row dml_by_rowid%rowtype;
     v_cnt int;
  begin
     v_cnt:=0;
     for v_row in (select * from dml_by_rowid x)
     loop
        v_cnt:=v_cnt+1;
        delete from orders where rowid=v_row.rid;
        if mod(v_cnt,1000)=0 then
          commit;
          dbms_lock.sleep(1);                    --change the sleep time as required
        end if;
        DBMS_APPLICATION_INFO.SET_CLIENT_INFO('processed '||v_cnt||' rows');
     end loop;
     commit;
     dbms_output.put_line('Total '||v_cnt||' processed');
  end;
  /

Step3. Check the progress of the session

SQL> select sid,logon_time,client_info from v$session where client_info like ‘processed%’;

 

 

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How to update/delete millions of rows from a big table safely?

  1. yogesh says:

    Nice Document..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: