Oracle dbms_rectifier_diff Oracle : eygle (eygle.com@gmail.com dbms_rectifier_diff Oracle dbms_rectifier_diff : http://www.eygle.com/archives/2005/01/eoadbms_rectifi.html 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 =>100 16 ; 17 end; 18 / http://www.eygle.com - 1 -
PL/SQL procedure successfully completed. www.eygle.com 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 http://www.eygle.com - 2 -
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 http://www.eygle.com - 3 -
IS SELECT "DATLOGONTIME", "NUMGENDER", "NUMSTATUS", "NUMUSERID", "VC2IP", "VC2USERNAME" FROM "HAWA"."TEST"@authaa.coolyoung.com.cn 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" http://www.eygle.com - 4 -
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 http://www.eygle.com - 5 -
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:00.16 http://www.eygle.com - 6 -
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:32.49 3. SQL> create table hawa.missing_rows_hw_userprofile 2 as 3 select * from hawa.hw_userprofile where 1=0; Table created. Elapsed: 00:00:00.12 4. (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:00.04 5. SQL> insert into hawa.missing_rows_hw_userprofile 2 select * from hawa.hw_userprofile where NUMUSERID in 3 (select * from hawa.prof; http://www.eygle.com - 7 -
263 rows created. www.eygle.com Elapsed: 00:00:00.06 SQL> commit; Commit complete. Elapsed: 00:00:00.02 6. 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:00.06 7. 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 =>100 http://www.eygle.com - 8 -
14 ; 15 END; 16 / www.eygle.com PL/SQL procedure successfully completed. Elapsed: 00:00:03.53 8. SQL> select count(* from hawa.hw_userprofile; COUNT(* ---------- 1746300 Elapsed: 00:00:02.22 SQL> select count(* from hawa.hw_userprofile@authaa; COUNT(* ---------- 1746300 Elapsed: 00:00:00.21 SQL> select count(* from hawa.hw_userprofile; COUNT(* ---------- 1746300 Elapsed: 00:00:00.59 SQL>select count(* from hawa.hw_userprofile@authaa; COUNT(* ---------- 1746300 Elapsed: 00:00:00.20 SQL> select NUMUSERID from hawa.hw_userprofile http://www.eygle.com - 9 -
2 minus www.eygle.com 3 select NUMUSERID from hawa.hw_userprofile@authaa ; no rows selected Elapsed: 00:00:23.51 SQL> http://www.eygle.com - 10 -
eygle ITPUB MS, itpub Oracle.,, Oracle ERP,. Oracle, Oracle., DBA,. 30,. SQL Oracle,, itpub dba itpub. Oracle DBA. http://www.eygle.com. http://www.eygle.com - 11 -