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

楊奇龍發表於2011-11-24
Oracle
    生產環境中遇到更新或者刪除大批量資料的時候，不能直接進行操作，要批量進行。
1 獲取要進行更新的資料的主鍵，儲存為文字檔案或者csv檔案。這一步一定要正確，否則下面的操作會造成更新錯誤的資料，造成資料不一致！
2 建立臨時表並將獲取的資料主鍵匯入到建立！
#!/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 <> $LOG_FILE
select sysdate from dual;
--1.--Create Temp Tables
create table yang.bak_da_20111123_1740 as select LONGID from yang.udb where 0=1;
--2.--Load ID need to be DA with sqlldr
set define off
! nohup sqlldr yang/yang control=./yang_udb_20111123_1740.ctl direct=true log=./yang_udb_20111123_1740.${today}.log > sqlldr_da-of-20111123-1740.ctl.`date +%Y%m%d-%H%M%S`.yql.log 2>&1 &
set define on
--3.--Backup Original Data to TempTable
select * from user_objects where created > sysdate-1;
select sysdate from dual;
exit;
EOF
return $?
}
im_dz &&
echo -e "`date +%Y%m%d-%H%M%S`: dz_20111123-1740_step1 DA(Data Admendment) is started by yangql - SUCCESS " >> $LOG_FILE||
echo -e "`date +%Y%m%d-%H%M%S`: dz_20111123-1740_step1 DA(Data Admendment) is started by yangql - FAIL " >> $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)
#####################
3 使用一個過程，每隔更新1000行，commit一次並刪除備份表中的資料。
--cat > dz_20111123_1820_step3.sh
#!/bin/sh 
# Created by yangql @ 2011-11-23
# Parameters 
## . /home/oracle/.profile
ORA_USER=yang
ORA_PASSWD=yang
TODAY=`date +"%F"`
# LOG_FILE=/tmp/oracle/${ORA_USER}_${TODAY}_log
ALERT_LOG=/opt/oracle/admin/alisoft/bdump/alert_im1.log
dz()
{
sqlplus ${ORA_USER}/$ORA_PASSWD <
@L_MyLoginU --
select sysdate from dual;
@dz_20111123_1820_step3.sql
select sysdate from dual;
exit;
EOF
return $?
}

dz &&
echo -e "`date +%Y%m%d-%H%M%S`: dz_20111123_1820_step2 DA(Data Admendment) is started by yangql- SUCCESS " >> $ALERT_LOG ||
echo -e "`date +%Y%m%d-%H%M%S`: dz_20111123_1820_step2 DA(Data Admendment) is started by yangql- FAIL " >> $ALERT_LOG
exit 0
---執行過程的指令碼：
--cat > 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;
begin
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);
end;
/

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-711925/，如需轉載，請註明出處，否則將追究法律責任。

相關文章