Oracle高级复制冲突解决机制的研究

Size: px

Start display at page:

Download "Oracle高级复制冲突解决机制的研究"

顺堡阎
5 years ago
Views:

1 Oracle dbms_rectifier_diff Oracle : eygle (eygle.com@gmail.com dbms_rectifier_diff Oracle dbms_rectifier_diff : DIFFERENCES Oracle dbms_rectifier_diff.differences minus Oracle : SQL> alter session set events '10046 trace name context forever,level 12'; Session altered. Elapsed: 00:00:00.02 SQL> begin dbms_rectifier_diff.differences( 2 SNAME1 =>'HAWA', 3 ONAME1 =>'TEST', 4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', 4 SNAME2 =>'HAWA', 6 ONAME2 =>'TEST', 7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', 8 WHERE_CLAUSE =>NULL, 9 COLUMN_LIST =>NULL, 10 MISSING_ROWS_SNAME =>'HAWA', 11 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST', 12 MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST', 13 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', 14 MAX_MISSING =>500, 15 COMMIT_ROWS => ; 17 end; 18 /

2 PL/SQL procedure successfully completed. Elapsed: 00:00:01.97 SQL> alter session set events '10046 trace name context off'; ( : 1. Minus DECLARE row_count BINARY_INTEGER := 0; missing_rows BINARY_INTEGER := 0; arowid ROWID; CURSOR c IS SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" FROM "HAWA"."TEST" MINUS SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" FROM "HAWA"."TEST"@authaa.coolyoung.com.cn; BEGIN FOR r IN c LOOP missing_rows := missing_rows + 1; IF missing_rows > 500 THEN EXIT; END IF; INSERT INTO "HAWA"."MISSING_ROWS_TEST" ("DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" VALUES (r."datlogontime", r."numgender", r."numstatus", r."numuserid", r."vc2ip", r."vc2username" ; SELECT ROWID INTO arowid

3 FROM "HAWA"."MISSING_ROWS_TEST" WHERE ( datlogontime = r."datlogontime" OR (datlogontime IS NULL AND r."datlogontime" IS NULL AND ( numgender = r."numgender" OR (numgender IS NULL AND r."numgender" IS NULL AND ( numstatus = r."numstatus" OR (numstatus IS NULL AND r."numstatus" IS NULL AND (numuserid = r."numuserid" AND (vc2ip = r."vc2ip" OR (vc2ip IS NULL AND r."vc2ip" IS NULL AND ( vc2username = r."vc2username" OR (vc2username IS NULL AND r."vc2username" IS NULL ; INSERT INTO "HAWA"."MISSING_LOCATION_TEST" (present, absent, r_id VALUES ('AVATAR.COOLYOUNG.COM.CN', 'AUTHAA.COOLYOUNG.COM.CN', arowid ; row_count := row_count + 1; IF row_count >= 100 THEN row_count := 0; END IF; END LOOP; END; 2. Minus DECLARE row_count BINARY_INTEGER := 0; missing_rows BINARY_INTEGER := 0; arowid ROWID; CURSOR c

4 IS SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" FROM MINUS SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" FROM "HAWA"."TEST"; BEGIN FOR r IN c LOOP missing_rows := missing_rows + 1; IF missing_rows > 500 THEN EXIT; END IF; INSERT INTO "HAWA"."MISSING_ROWS_TEST" ("DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" VALUES (r."datlogontime", r."numgender", r."numstatus", r."numuserid", r."vc2ip", r."vc2username" ; SELECT ROWID INTO arowid FROM "HAWA"."MISSING_ROWS_TEST" WHERE ( AND ( AND ( datlogontime = r."datlogontime" OR (datlogontime IS NULL AND r."datlogontime" IS NULL numgender = r."numgender" OR (numgender IS NULL AND r."numgender" IS NULL numstatus = r."numstatus" OR (numstatus IS NULL AND r."numstatus" IS NULL AND (numuserid = r."numuserid" AND (vc2ip = r."vc2ip" OR (vc2ip IS NULL AND r."vc2ip" IS NULL AND ( vc2username = r."vc2username"

5 OR (vc2username IS NULL AND r."vc2username" IS NULL ; INSERT INTO "HAWA"."MISSING_LOCATION_TEST" (present, absent, r_id VALUES ('AUTHAA.COOLYOUNG.COM.CN', 'AVATAR.COOLYOUNG.COM.CN', arowid ; row_count := row_count + 1; IF row_count >= 100 THEN row_count := 0; END IF; END LOOP; END; Oracle Oracle column list, ( WHERE_CLAUSE COLUMN_LIST WHERE_CLAUSE NUMGENDER=1 " " COLUMN_LIST

6 NUMUSERID ID NUMUSERID NUMUSERID MISSING_ROWS_TEST begin dbms_rectifier_diff.differences( SNAME1 =>'HAWA', ONAME1 =>'TEST', REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', SNAME2 =>'HAWA', ONAME2 =>'TEST', COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', WHERE_CLAUSE =>'NUMGENDER=1', COLUMN_LIST =>'NUMUSERID', MISSING_ROWS_SNAME =>'HAWA', MISSING_ROWS_ONAME1 =>'MISSING_ROWS_TEST', MISSING_ROWS_ONAME2 =>'MISSING_LOCATION_TEST', MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', MAX_MISSING =>500, COMMIT_ROWS =>100 ; end; / Ok DIFFERENCES : 1. ID SQL> create table hawa.prof as select NUMUSERID from hawa.hw_user where 1=0; Table created. Elapsed: 00:00:

7 2. A B A>B SQL> insert into hawa.prof 2 select * from 3 ( 4 select NUMUSERID from hawa.hw_userprofile 5 minus 6 select NUMUSERID from hawa.hw_userprofile@authaa 7 / 263 rows created. Elapsed: 00:00: SQL> create table hawa.missing_rows_hw_userprofile 2 as 3 select * from hawa.hw_userprofile where 1=0; Table created. Elapsed: 00:00: (Location Oracle ROWID ROWID missing_rows_hw_userprofile SQL> create table hawa.missing_loc_hw_userprofile ( 2 present VARCHAR2(128, 3 absent VARCHAR2(128, 4 r_id ROWID; Table created. Elapsed: 00:00: SQL> insert into hawa.missing_rows_hw_userprofile 2 select * from hawa.hw_userprofile where NUMUSERID in 3 (select * from hawa.prof;

8 263 rows created. Elapsed: 00:00:00.06 SQL> commit; Commit complete. Elapsed: 00:00: ROWID SQL> insert into hawa.missing_loc_hw_userprofile 2 select 'AVATAR.COOLYOUNG.COM.CN','AUTHAA.COOLYOUNG.COM.CN',rowid from hawa.missing_rows_hw_userprofile; 263 rows created. Elapsed: 00:00:00.00 SQL> commit; Commit complete. Elapsed: 00:00: SQL> BEGIN DBMS_RECTIFIER_DIFF.RECTIFY( 2 SNAME1 =>'HAWA', 3 ONAME1 =>'HW_USERPROFILE', 4 REFERENCE_SITE =>'AVATAR.COOLYOUNG.COM.CN', 5 SNAME2 =>'HAWA', 6 ONAME2 =>'HW_USERPROFILE', 7 COMPARISON_SITE =>'AUTHAA.COOLYOUNG.COM.CN', 8 COLUMN_LIST =>NULL, 9 MISSING_ROWS_SNAME =>'HAWA', 10 MISSING_ROWS_ONAME1 =>'MISSING_ROWS_HW_USERPROFILE', 11 MISSING_ROWS_ONAME2 =>'MISSING_LOC_HW_USERPROFILE', 12 MISSING_ROWS_SITE =>'AVATAR.COOLYOUNG.COM.CN', 13 COMMIT_ROWS =>

9 14 ; 15 END; 16 / PL/SQL procedure successfully completed. Elapsed: 00:00: SQL> select count(* from hawa.hw_userprofile; COUNT(* Elapsed: 00:00:02.22 SQL> select count(* from hawa.hw_userprofile@authaa; COUNT(* Elapsed: 00:00:00.21 SQL> select count(* from hawa.hw_userprofile; COUNT(* Elapsed: 00:00:00.59 SQL>select count(* from hawa.hw_userprofile@authaa; COUNT(* Elapsed: 00:00:00.20 SQL> select NUMUSERID from hawa.hw_userprofile

10 2 minus 3 select NUMUSERID from hawa.hw_userprofile@authaa ; no rows selected Elapsed: 00:00:23.51 SQL>

11 eygle ITPUB MS, itpub Oracle.,, Oracle ERP,. Oracle, Oracle., DBA,. 30,. SQL Oracle,, itpub dba itpub. Oracle DBA

Microsoft Word - 高级复制 - Advanced Replication初步研究.doc

Microsoft Word - 高级复制 - Advanced Replication初步研究.doc 关于 Advanced Replication 的初步研究 Author:Kamus, yangtingkun, eygle Mail:kamus@itpub.net Date:2004 年 3 月一概述...1 二 MR 的概念和构架...3 三冲突解决方案的概念和构架...9 四冲突解决机制的研究...10 五解决数据