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
     v_row dml_by_rowid%rowtype;
     v_cnt int;
     for v_row in (select * from dml_by_rowid x)
        delete from orders where rowid=v_row.rid;
        if mod(v_cnt,1000)=0 then
          dbms_lock.sleep(1);                    --change the sleep time as required
        end if;
        DBMS_APPLICATION_INFO.SET_CLIENT_INFO('processed '||v_cnt||' rows');
     end loop;
     dbms_output.put_line('Total '||v_cnt||' processed');

Step3. Check the progress of the session

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




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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: