【oracle】更新大批量資料變更步驟

<b>    生産環境中遇到更新或者删除大批量資料的時候，不能直接進行操作，要批量進行。</b>

<b>1 擷取要進行更新的資料的主鍵，</b>儲存為文本檔案或者csv檔案。這一步一定要正确，否則下面的操作會造成更新錯誤的資料，造成資料不一緻！

<b>2 建立臨時表并将擷取的資料主鍵導入到建立！</b>

#!/bin/sh 

# created by yangql on 2011-11-23

# parameters 

## . /home/oracle/.profile

ora_user=yang

ora_passwd=yang

today=`date +"%f"`

log_file=/home/oracle/yangql/${ora_user}_${today}.log

im_dz()

{

sqlplus -s ${ora_user}/$ora_passwd &lt;&gt; $log_file

select sysdate from dual;

<b>--1.--create temp tables</b>

<b>create table yang.bak_da_20111123_1740 as select longid from yang.udb where 0=1;</b><b> </b>

<b>--2.--load id need to be da with sqlldr</b>

set define off

<b>! nohup sqlldr yang/yang control=./yang_udb_20111123_1740.ctl direct=true log=./yang_udb_20111123_1740.${today}.log &gt; sqlldr_da-of-20111123-1740.ctl.`date +%y%m%d-%h%m%s`.yql.log 2&gt;&amp;1 &amp;</b>

set define on

<b>--3.--backup original data to temptable</b>

select * from user_objects where created &gt; sysdate-1;

exit;

eof

return $?

}

im_dz &amp;&amp;

echo -e "`date +%y%m%d-%h%m%s`: dz_20111123-1740_step1 da(data admendment) is started by yangql - success " &gt;&gt; $log_file||

echo -e "`date +%y%m%d-%h%m%s`: dz_20111123-1740_step1 da(data admendment) is started by yangql - fail " &gt;&gt; $log_file

exit 0

######################

#sqlldr 導入的控制檔案

#load data

#infile '/home/oracle/yangql/20111123_yang_udb.csv'

#insert

#into table yang.bak_da_20111123_1740 fields terminated by 'chr(10)' (longid)

#####################

<b>3 使用一個過程，每隔更新1000行，commit一次并删除備份表中的資料。</b>

--cat &gt; dz_20111123_1820_step3.sh

# created by yangql @ 2011-11-23

# log_file=/tmp/oracle/${ora_user}_${today}_log

alert_log=/opt/oracle/admin/alisoft/bdump/alert_im1.log

dz()

sqlplus ${ora_user}/$ora_passwd &lt;@l_myloginu --

@dz_20111123_1820_step3.sql

dz &amp;&amp;

echo -e "`date +%y%m%d-%h%m%s`: dz_20111123_1820_step2 da(data admendment) is started by yangql- success " &gt;&gt; $alert_log ||

echo -e "`date +%y%m%d-%h%m%s`: dz_20111123_1820_step2 da(data admendment) is started by yangql- fail " &gt;&gt; $alert_log

---執行過程的腳本：

--cat &gt; dz_20111123_1820_step3.sql

-- created by yangql @ 2011-11-23

declare

cursor cur is

  select 

      longid

      ,rowid

  from yang.bak_da_20111123_1740

      -- where user_value is null

  ;

  v_count number:=0;

  v_commit_count number:=0;

  v_limit number :=1000;

----

  v_lcl__user_value number;

  v_lcl__alipay_auth number;

begin

 for x in cur

      loop 

        v_count:=v_count+1;

update yang.udb t set t.contactshowflag=1 where t.longid = x.longid;

    exception

when no_data_found then

     dbms_output.put_line('----exception--: longid '''||x.longid||''' is not found in table yang.udb !!!');

end;

delete yang.bak_da_20111123_1740

where rowid=x.rowid;

if v_count=v_limit then

   v_commit_count:=v_commit_count+1;

   commit;

     dbms_output.put_line('--records updated and commited: '||v_count||' * '||v_commit_count);

   v_count:=0;

 end if;   

end loop;

commit;

dbms_output.put_line('--records updated and commited - last batch: '||v_count);

/

&lt;&gt;