--第3次测试（再次插入1000条符合筛选条件的记录，status=9) SQL> begin
2 for i in 1..1000 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.
SQL> exec detach_pro;
PL/SQL procedure successfully completed.
SQL> select count(*) from tabdet;
COUNT(*) ---------- 9000
SQL> select count(*) from tabhdr;
COUNT(*) ---------- 10000
SQL> select count(*) from arch_tabdet;
COUNT(*) ---------- 102000
SQL> select count(*) from arch_tabhdr;
COUNT(*) ---------- 101000
SQL> select * from arch_log;
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ---------------------------------------------------------------------------------------------------------------------------------- 2014-12-30 10:30:45 2014-12-30 10:30:53 2014-12-30 10:31:02 100000 100000 100000 100000 success
2014-12-30 10:43:38 2014-12-30 10:43:38 2014-12-30 10:43:38 0 0 0 0 success
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ----------------------------------------------------------------------------------------------------------------------------------
2014-12-30 10:44:28 2014-12-30 10:44:28 2014-12-30 10:44:28 0 0 0 0 success
2014-12-30 10:53:15 2014-12-30 10:53:15 2014-12-30 10:53:15 2000 2000 1000 1000 success
ARCHBEGINTIME ARCHMIDDLETIME ARCHENDTIME ARCHINSCOUNT1 ARCHDELCOUNT1 ARCHINSCOUNT2 ARCHDELCOUNT2 ------------------- ------------------- ------------------- ------------- ------------- ------------- ------------- ARCHSTATUS ARCHERRORCODE -------------------- -------------------- ARCHERRORMSG ----------------------------------------------------------------------------------------------------------------------------------
第一次迁移操作： tabdet表中的2000行记录被插入到arch_tabdet表中，然后删除tabdet表中的2000行相应记录； 因此结果是tabdet表剩下9000条记录，arch_tabdet表增加到12000条记录。
第二次迁移操作：
tabhdr表中的1000行记录被插入到arch_tabhdr表中，然后删除tabhdr表中的1000行相应记录； 因此结果是tabhdr表剩下10000条记录，arch_tabhdr表增加到11000条记录。
在实际生产中，具体是哪些符合迁移条件的表是根据存储过程中具体的where条件来定的，这里的测试并不一定很准确。
最后，可以通过创建job来定期自动运行存储过程，如： declare
v_jobnum number; begin
dbms_job.submit(v_jobnum,'detach_pro',sysdate,'sysdate+1/24');
end; commit; 或：
declare v_jobnum number;
begin
dbms_job.submit
( job => v_jobnum
,what => 'detach_pro'
,next_date => sysdate
,interval => 'SYSDATE+1/24'
,no_parse => TRUE
);
end;
/
commit;
?
--把存储过程防止到job中，每小时自动运行
?
SQL> declare v_jobnum number;
2 begin
3 dbms_job.submit
4 ( job => v_jobnum
5 ,what => 'detach_pro'
6 ,next_date => sysdate
7 ,interval => 'SYSDATE+1/24'
8 ,no_parse => TRUE
9 );
10 end;
11 /
?
PL/SQL procedure successfully completed.
?
SQL> commit;
?
Commit complete.
?
SQL> desc user_jobs;
Name Null? Type
----------------------------------------------------------