生产环境中遇到更新或者删除大批量数据的时候，不能直接进行操作，要批量进行。
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;
/
<>