Oracle 大表复制方案存储过程实现

Oracle 大表复制方案存储过程实现

1. procedure makePayPlayIraBakNew is  
2.      
3.     v_num number;   
4.   begin  
5.      
6.   vanceinfo.gmac_log_pro.setloginfo('makePayPlayIraBakNew',   
7.                                       '1114',   
8.                                       '9',   
9.                                       sysdate,   
10.                                       'makePayPlayIraBakNew执行开始');   
11.     v_num := 0;   
12.     select count(1)   
13.       into v_num   
14.       from user_tables   
15.      where table_name = 'GMAC_PAYPLAY_IRA_HIS';   
16.   if(v_num > 0) then  
17.     execute immediate 'DROP TABLE  GMAC_PAYPLAY_IRA_HIS';   
18.   end if;   
19.        
20.     execute immediate 'CREATE TABLE GMAC_PAYPLAY_IRA_HIS AS SELECT * FROM GMAC_PAYPLAY';   
21.      
22.     vanceinfo.gmac_log_pro.setloginfo('makePayPlayIraBakNew',   
23.                                       '1114',   
24.                                       '0',   
25.                                       sysdate,   
26.                                       'makePayPlayIraBakNew执行成功结束');   
27.       
28.   exception   
29.     when others then  
30.       vanceinfo.gmac_log_pro.setloginfo('makePayPlayIraBakNew',   
31.                                         '1114',   
32.                                         '9',   
33.                                         sysdate,   
34.                                         substr(sqlerrm, 1, 200));                                      
35.   end makePayPlayIraBakNew;