【oracle】更新大批量数据变更步骤

  1. 云栖社区>
  2. 博客>
  3. 正文

【oracle】更新大批量数据变更步骤

北在南方 2016-04-14 09:53:59 浏览2446
展开阅读全文
    生产环境中遇到更新或者删除大批量数据的时候,不能直接进行操作,要批量进行。
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;
/
<>

网友评论

登录后评论
0/500
评论