下面我来模拟一下这个过程： 由于没有拿到具体的建表语句，这里把表的内容最简化，只留2个列，作为最基本的演示
--连接到测试用户，创建测试表 SQL> conn zlm/zlm Connected. SQL> select * from cat;
no rows selected SQL> create table tabhdr(tabhdrid number(10),status number(10));
Table created.
SQL> create table tabdet(tabhdrid number(10));
Table created.
SQL> create table arch_tabdet as select * from tabdet;
Table created.
Table created.
--创建操作日志表 SQL> create table arch_log( 2 archbegintime char(19), 3 archmiddletime char(19), 4 archendtime char(19), 5 archinscount1 number, 6 archdelcount1 number, 7 archinscount2 number, 8 archdelcount2 number, 9 archstatus varchar2(20), 10 archerrorcode varchar2(20), 11 archerrormsg varchar2(1000));
Table created.
操作日志表字段说明： archbegintime->archimiddletime //第一次迁移操作（insert+delete）的时间 archimiddletime->archendtime //第二次迁移操作（insert+delete）的时间 archcount1 //第一次迁移操作（insert+delete）的数据量 archcount2 //第二次迁移操作（insert+delete）的数据量 archstatus //操作状态（success/failure） archerrorcode //报错代码 archerrormsg //报错信息
--插入测试数据（每个表插入10W条记录，仅测试功能没必要用很大的数据） SQL> begin 2 for i in 1..100000 3 loop 4 insert into tabhdr values(i,9); 5 insert into tabdet values(i); 6 end loop; 7 commit; 8 end; 9 /
PL/SQL procedure successfully completed.
--创建存储过程detach_pro SQL> create or replace procedure detach_pro 2 is 3 maxrows number default 10000; 4 rowid_table dbms_sql.Urowid_Table; 5 i number; 6 cursor cur_1 is SELECT a.rowid FROM tabdet a WHERE tabhdrid IN(SELECT tabhdrid FROM tabhdr WHERE STATUS=9) order by a.rowid; 7 cursor cur_2 is SELECT a.rowid FROM tabhdr a WHERE status=9 order by a.rowid; 8 9 v_begintime char(19):=to_char(sysdate,'yyyy-mm-dd hh:mi:ss'); 10 v_middletime char(19); 11 v_inscount1 number:=0; 12 v_delcount1 number:=0; 13 v_inscount2 number:=0; 14 v_delcount2 number:=0; 15 v_errcode varchar2(100); 16 v_errerrm varchar2(1000); 17 18 begin 19 open cur_1; 20 loop 21 exit when cur_1%notfound; 22 fetch cur_1 bulk collect into rowid_table limit maxrows; 23 24 forall i in 1 .. rowid_table.count 25 insert into arch_tabdet select * from tabdet where rowid = rowid_table(i); 26 commit; 27 v_inscount1:=v_inscount1+rowid_table.count; 28 forall i in 1 .. rowid_table.count 29 delete from tabdet where rowid = rowid_table(i); 30 commit; 31 v_delcount1:= v_delcount1+rowid_table.count; 32 end loop; 33 close cur_1; 34 v_middletime:=to_char(sysdate,'yyyy-mm-dd hh:mi:ss'); 35 open cur_2; 36 loop 37 exit when cur_2%notfound; 38 fetch cur_2 bulk collect into rowid_table limit maxrows; 39 40 forall i in 1 .. rowid_table.count 41 insert into arch_tabhdr select * from tabhdr where rowid = rowid_table(i); 42 commit; 43 v_inscount2:=v_inscount2+rowid_table.count; 44 forall i in 1 .. rowid_table.count 45 delete from tabhdr where rowid = rowid_table(i); 46 commit; 47 v_delcount2:= v_delcount2+rowid_table.count; 48 end loop; 49 close cur_2; 50 insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-dd hh:mi:ss'), v_inscount1,v_delcount1,v_inscount2,v_delcount2,'success',null,null); 51 commit; 52 exception 53 when others then 54 v_errcode :=sqlcode; 55 v_errerrm :=sqlerrm; insert into arch_log values (v_begintime,v_middletime,to_char(sysdate,'yyyy-mm-d