通过rowid逻辑并行抽取数据

在我们迁移数据，或者进行同步数据的时候,对于应用变更频繁的表进行抽取数据，经常会碰到oracle需要读取回滚段，会导致很慢，有时候甚至会报ora01555错误，

比如我们有个表比较大是40G左右，就是一个月的按月分区数据，这个时候如果想尽快抽取数据到另外一个库，有几种方法：

方法1：

     大家都知道的使用append，然后不写日志，parallel抽取方式:

代码类似如下:

[sql]  view plain copy

1. alter session enable parallel DML;  
2. ALTER SESSION SET db_file_multiblock_read_count=128;  
3. INSERT /*+append parallel(b 2)*/  
4. INTO OS_USER_SERVICE_HIS_1 b  
5.   SELECT /*+FULL(a) PARALLEL(A,2)*/  
6.    * FROM OS_USER_SERVICE_HIS A  
7.    WHERE CREATETIME >= TO_DATE('20110906', 'yyyymmdd');  

方法2：

       开4个会话:

通过createtime逻辑上进行4个时间区间的并行处理：比如一个月的话，分成1会话处理第一周，然后一直到4会话处理第4周

，当然你也可以再细分：1会话写循环一小时一小时处理。

方法3：

      使用rowid并行：

这里我重要说下使用rowid并行的方法:

真实案例:

[sql]  view plain copy

1. create table ROWID_OS_USER_BEHAVIOR_201212  
2. (  
3.   ID        NUMBER,  
4.   ROWID_MIN VARCHAR2(32),  
5.   ROWID_MAX VARCHAR2(32),  
6.   FLAG      NUMBER  
7. );  

首先创建rowid保存表:

获取远程库的data_object_id:

[sql]  view plain copy

1. SQL> select data_object_id from Dba_objects@mail139.localdomain where object_name='OS_USER_BEHAVIOR_MONTH'  and subobject_name='OS_USER_BEHAVIOR_MONTH2012M12'  
2.   2  ;  
3.    
4. DATA_OBJECT_ID  
5. --------------  
6.         218043  

--获取远程库的最小，最大rowid:

[sql]  view plain copy

1. SQL> insert into rowid_os_user_behavior_201212(id,rowid_min,rowid_max,FLAG)  
2.   2    select rownum,  
3.   3          DBMS_ROWID.ROWID_CREATE@mail139.localdomain(1,218043,e.RELATIVE_FNO,e.BLOCK_ID,0),  
4.   4          DBMS_ROWID.ROWID_CREATE@mail139.localdomain(1,218043,e.RELATIVE_FNO,e.BLOCK_ID+e.BLOCKS-1,10000),  
5.   5          0  
6.   6          from dba_extents@mail139.localdomain e where e.segment_name='OS_USER_BEHAVIOR_MONTH'  
7.   7                                                      and e.owner='OSS01'  
8.   8                                                      and partition_name='OS_USER_BEHAVIOR_MONTH2012M12'  
9.   9  ;  
10.    
11. 659 rows inserted;  
12.   
13. commit;  

--将远程这个分区对应的extents范围的rowid放入表中:

插入完之后，查询结果如下:

SQL> select * from rowid_os_user_behavior_201212 where flag =0 and rownum =1;
 
        ID ROWID_MIN                        ROWID_MAX                              FLAG
---------- -------------------------------- -------------------------------- ----------
       422 AAA1O7AAxAADDgJAAA               AAA1O7AAxAADFgICcQ                        0

编写拉数据存储过程:  如下:

[sql]  view plain copy

1. create or replace procedure p_ods_os_user_beha_month(i integer) is  
2.   vSTATEDATE         dbms_sql.NUMBER_Table;  
3.   vUSERNUMBER        dbms_sql.VARCHAR2_Table;  
4.   vSERVICEID         dbms_sql.NUMBER_Table;  
5.   vOPERTYPE          dbms_sql.NUMBER_Table;  
6.   vRECVCOUNT         dbms_sql.NUMBER_Table;  
7.   vSENDCOUNT         dbms_sql.NUMBER_Table;  
8.   vTOTALCOUNT        dbms_sql.NUMBER_Table;  
9.   vPRESENDCOUNT      dbms_sql.NUMBER_Table;  
10.   vENTERPRISEFLAG    dbms_sql.NUMBER_Table;  
11.   vENTERPRISESHEETNO dbms_sql.VARCHAR2_Table;  
12.   vCREATETIME        dbms_sql.DATE_Table;  
13.   vMODIFYTIME        dbms_sql.DATE_Table;  
14.   vPROVCODE          dbms_sql.NUMBER_Table;  
15.   vSERVICEITEM       dbms_sql.VARCHAR2_Table;  
16.   vCARDTYPE          dbms_sql.NUMBER_Table;  
17.   vAREACODE          dbms_sql.NUMBER_Table;  
18.   vBINDTYPEID        dbms_sql.NUMBER_Table;  
19.   vORDERTYPE         dbms_sql.NUMBER_Table;  
20.   vMAILSERVICEITEM   dbms_sql.VARCHAR2_Table;  
21. /*  vCounter           number := 1;*/  
22.   vCounter_out       number := 0;  
23.   cur_syncdata       sys_refcursor;  
24. begin  
25.   for x in (select *  
26.               from rowid_OS_USER_BEHAVIOR_201212  
27.              where mod(id, 4) = i  ---这里就是变量i;  
28.                and flag = 0) loop  
29.     begin  
30.       open cur_syncdata for  
31.         select /*+rowid(t))*/  
32.          STATEDATE,  
33.          USERNUMBER,  
34.          SERVICEID,  
35.          OPERTYPE,  
36.          RECVCOUNT,  
37.          SENDCOUNT,  
38.          TOTALCOUNT,  
39.          PRESENDCOUNT,  
40.          ENTERPRISEFLAG,  
41.          ENTERPRISESHEETNO,  
42.          CREATETIME,  
43.          MODIFYTIME,  
44.          PROVCODE,  
45.          SERVICEITEM,  
46.          CARDTYPE,  
47.          AREACODE,  
48.          BINDTYPEID,  
49.          ORDERTYPE,  
50.          MAILSERVICEITEM  
51.           from <a href="mailto:readonly.vw_os_user_behavior_mon1212@mail139.localdomain">readonly.vw_os_user_behavior_mon1212@mail139.localdomain</a> t  
52.          where rowid >= chartorowid(x.rowid_min)  
53.            and rowid <= chartorowid(x.rowid_max);  
54.       loop  
55.         begin  
56.           fetch cur_syncdata bulk collect  
57.             into vSTATEDATE, vUSERNUMBER, vSERVICEID, vOPERTYPE, vRECVCOUNT, vSENDCOUNT, vTOTALCOUNT, vPRESENDCOUNT, vENTERPRISEFLAG, vENTERPRISESHEETNO, vCREATETIME, vMODIFYTIME, vPROVCODE, vSERVICEITEM, vCARDTYPE, vAREACODE, vBINDTYPEID, vORDERTYPE, vMAILSERVICEITEM limit 5000;  
58.           forall row in 1 .. vUSERNUMBER.count()  
59.             insert into OS_USER_BEHAVIOR_MONTH_201212  
60.               (STATEDATE,  
61.                USERNUMBER,  
62.                SERVICEID,  
63.                OPERTYPE,  
64.                RECVCOUNT,  
65.                SENDCOUNT,  
66.                TOTALCOUNT,  
67.                PRESENDCOUNT,  
68.                ENTERPRISEFLAG,  
69.                ENTERPRISESHEETNO,  
70.                CREATETIME,  
71.                MODIFYTIME,  
72.                PROVCODE,  
73.                SERVICEITEM,  
74.                CARDTYPE,  
75.                AREACODE,  
76.                BINDTYPEID,  
77.                ORDERTYPE,  
78.                MAILSERVICEITEM)  
79.             values  
80.               (vSTATEDATE(row),  
81.                vUSERNUMBER(row),  
82.                vSERVICEID(row),  
83.                vOPERTYPE(row),  
84.                vRECVCOUNT(row),  
85.                vSENDCOUNT(row),  
86.                vTOTALCOUNT(row),  
87.                vPRESENDCOUNT(row),  
88.                vENTERPRISEFLAG(row),  
89.                vENTERPRISESHEETNO(row),  
90.                vCREATETIME(row),  
91.                vMODIFYTIME(row),  
92.                vPROVCODE(row),  
93.                vSERVICEITEM(row),  
94.                vCARDTYPE(row),  
95.                vAREACODE(row),  
96.                vBINDTYPEID(row),  
97.                vORDERTYPE(row),  
98.                vMAILSERVICEITEM(row));  
99.           vCounter_out := vCounter_out + sql%rowcount;  
100.   
101.           commit;  
102.           /*   if vCounter = 1000 then  
103.             begin  
104.               dbms_lock.sleep(3);  
105.               vCounter := 0;  
106.             end;  
107.           end if;*/  
108.           exit when cur_syncdata%notfound;  
109.         exception  
110.           when others then  
111.             dbms_output.put_line(sqlerrm);  
112.             rollback;  
113.             return;  
114.         end;  
115.       end loop;  
116.     end;  
117.     --更新处理的标记位：  
118.     update rowid_OS_USER_BEHAVIOR_201212 set flag = 1 where id = x.id;  
119.     commit;  
120.   end loop;  
121.   dbms_output.put_line('共处理' || vCounter_out || '条记录!');  
122. end;  

然后开4个会话，分别传入0,1,2,3即可:

30G的数据，经过测试并行4个进程，大概40分钟可以拉完，这里的应用在于拉的数据是经常需要dml的数据，优势比较明显。

转自：http://blog.youkuaiyun.com/huangchao_sky/article/details/8451077 道道博客