streams双向复制冲突规则(SET_UPDATE_CONFLICT_HANDLER)

streams双向复制环境下update的冲突解决方案(SET_UPDATE_CONFLICT_HANDLER)

 

method有4种，分别为

MAXIMUM,通过resolution_column的值来判断column list中column的值值该取哪个

MINIMUM 和MAXIMUM执行方式相同，通过resolution_column来判断

OVERWRITE 远程的操作将会覆盖本地提交的数据

DISCARD 远程的操作将会被丢弃，保留本地的数据

 

MAXIMUM method测试

 

表结构

 

CREATE TABLE TEST.AA
(
  ID    NUMBER,
  NUM   NUMBER,
  TIME  DATE                                    DEFAULT sysdate
)

 

ALTER TABLE TEST.AA ADD (
  CONSTRAINT AA_PK
 PRIMARY KEY
 (ID)

 

TABLESPACE USERS

 

在apply端建立冲突规则(所有节点)

 

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'ID';
  cols(2) := 'NUM';
  cols(3) := 'TIME';  --需要通过apply冲突检测的column值必须在resolution_column改变时改变，但是主键(ID)不需要
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'TEST.AA',
    method_name           =>  'MAXIMUM',
    resolution_column     =>  'NUM',
    column_list           =>  cols);
END;         
/            

commit; --需要commit

 

ORCL

 

update test.aa set num=60 ,time=sysdate where id=50;

 

GC

 

update test.aa set num=58 ,time=sysdate where id=50;

 

ORCL

 

commit;

 

GC

 

commit;

 

ORCL

 

SQL> select * from test.aa where id=50;

        ID        NUM TIME
---------- ---------- -------------------
        50         60 2010-06-27 21:22:36

 

GC

 

SQL> select * from test.aa where id=50;

        ID        NUM TIME
---------- ---------- -------------------
        50         60 2010-06-27 21:22:36

 

 

清除method MAXIMUM改为MINIMUM(所有节点)

 

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'ID';
  cols(2) := 'NUM';
  cols(3) := 'TIME';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'TEST.AA',
    method_name           =>  null,
    resolution_column     =>  'NUM',
    column_list           =>  cols);
END;         
/           

commit;

 

DECLARE
  cols  DBMS_UTILITY.NAME_ARRAY;
BEGIN
  cols(1) := 'ID';
  cols(2) := 'NUM';
  cols(3) := 'TIME';
  DBMS_APPLY_ADM.SET_UPDATE_CONFLICT_HANDLER(
    object_name           =>  'TEST.AA',
    method_name           =>  'MINIMUM',
    resolution_column     =>  'NUM',
    column_list           =>  cols);
END;         
/            

commit;

 

method MINIMUM测试

 

ORCL

 

update test.aa set num=40 ,time=sysdate where id=100;

 

GC

 

update test.aa set num=30 ,time=sysdate where id=100;

 

ORCL

 

commit;

 

GC

 

commit;

 

ORCL

 

SQL> select * from test.aa where id=100;

        ID        NUM TIME
---------- ---------- -------------------
       100         30 2010-06-27 21:27:45

 

GC

 

SQL> select * from test.aa where id=100;

        ID        NUM TIME
---------- ---------- -------------------
       100         30 2010-06-27 21:27:45

 

method OVERWRITE测试

 

ORCL

 

update test.aa set num=40 ,time=sysdate where id=110;

 

GC

 

update test.aa set num=50 ,time=sysdate where id=110;

 

ORCL

 

commit;

 

GC

 

commit;

 

ORCL

 

SQL> select * from test.aa where id=110;

        ID        NUM TIME
---------- ---------- -------------------
       110         40 2010-06-27 21:46:48

 

GC

 

SQL> select * from test.aa where id=110;

        ID        NUM TIME
---------- ---------- -------------------
       110         40 2010-06-27 21:46:48

 

DISCARD同理