【原创】Oracle11g新特性DBMS_COMP…

Oracle11g中引入了一个新的PL/SQL包DBMS_COMPARISON，可以比较不同数据库下或者schame

下的对象/schema/数据，并且可以根据规则将不同的数据进行同步。这在进行数据的分布时

将十分的有用，例如你的数据复制过程中出现问题，导致源数据和目标数据出现不一致，则

可以借用该特性进行处理。如果你的系统中该安装包，可以通过以下方法安装:
1、以sys身份登入数据库
2、执行dbmscmp.sql

可以利用DBMS_COMPARISON来执行比较的对象有：

表
基于单个表的视图
物化视图
以上三种对象的同义词

下面通过一个实验来体验这个新功能：

-----------------------------------------------------------------------------------------------

1.建立用户并授权
create user test1 identified by tiger;
create user test2 identified by tiger;

grant dba to test1,test2;

2.建立表并插入数据
create table stu
(
    stuno   number not null,
    stuname varchar2(20),
    classnovarchar2(5)    
);

alter table stu add constraint pk_stu  primary key(stuno);
 
insert into stu values(1,'tom','c5');
insert into stu values(2,'jack','c2');
insert into stu values(3,'jim','c3');
insert into stu values(4,'lily','c1');

commit;

select * from stu;
3.利用create_comparison建立比较任务(前提是比较对象上有指定的索引)

begin
dbms_comparison.create_comparison(comparison_name=>'COMPTEST',
                                schema_name=>'TEST1',
                                object_name=>'stu',
                                dblink_name=>NULL,
                                remote_schema_name=>'TEST2',
                                remote_object_name=>'stu');
end;
/

由于比较的都是在本地库，所以dblink_name设置为NULL。如果另外一个对象在远程库，则

远程库的版本只要在10R1版本以上即可。当然两个库的字符集必须一致。两个要比较的表的

列数如果不相同，则必须使用column_list参数列出两个对象中都存在的列进行比较。否则

会报以下错误：

ORA-23625: TEST1.TESTCOMP1 和 TEST2.TESTCOMP2@ 的表形式不匹配。
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 4197
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 420
ORA-06512: 在 line 2

要对对象进行比较，需要对象上有以下一种索引的存在：

基于数字类型(NUMBER | FLOAT | BINARY_FLOAT |BINARY_DOUBLE),timestamp类型

(TIMESTAMP | TIMESTAMP WITH TIME ZONE | and TIMESTAMP WITH LOCALTIME ZONE)，或

者Interval类型(INTERVAL YEAR TO MONTH | INTERVAL DAY TOSECOND)或者DATE类型的单

列索引。
只包含上述类型列的复合索引，并且其中每个列要么有NOT NULL约束，要么是主键的一部分

。
如果比较时的扫描模式选择的是CMP_SCAN_MODE_FULL或者CMP_SCAN_MODE_CUSTOM，索引可以

放宽条件，除了上面说的列类型，也可以包含VARCHAR2或者CHAR列。

目前还只能支持常规列类型的比较，对于LONG | LONG RAW | ROWID | UROWID | CLOB |

NCLOB | BLOB | BFILE | TYPE(包括用户自定义和Oracle预定义的类型)类型的列都还不能

进行较。

如果违反上述条件，欲比较的对象上缺乏所需要的索引的话，则会收到以下错误：

ORA-23626: 表 TEST1.TEST 上没有符合要求的索引
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 4197
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 420
ORA-06512: 在 line 2

4.执行compare过程进行比较（得到scan_id）

set serveroutput on

declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return := dbms_comparison.compare(comparison_name=>'COMPTEST',
scan_info=>compare_info,
perform_row_dif=>TRUE);

if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is datadivergence.');
dbms_output.put_line('Check the dba_comparison anddba_comparison_scan_summary

views for locate the differences forscan_id:'||compare_info.scan_id);
end if;
end;
/
5.根据得到的scan_id查询执行的结果：

查询以下视图获得比较结果

DBA_COMPARISON
USER_COMPARISON
DBA_COMPARISON_COLUMNS
USER_COMPARISON_COLUMNS
DBA_COMPARISON_SCAN
USER_COMPARISON_SCAN
DBA_COMPARISON_SCAN_SUMMARY
USER_COMPARISON_SCAN_SUMMARY
DBA_COMPARISON_SCAN_VALUES
USER_COMPARISON_SCAN_VALUES
DBA_COMPARISON_ROW_DIF
USER_COMPARISON_ROW_DIF
我们来看看比较的结果：

select a.owner, a.comparison_name, a.schema_name,

a.object_name,z.current_dif_count difference
from dba_comparison a, dba_comparison_scan_summary z
where a.comparison_name=z.comparison_name
and a.owner=z.owner and z.scan_id=24;

OWNER COMPARISON_NAME SCHEMA_NAME  OBJECT_NAME DIFFERENCE
----- --------------- ------------- ------------- ---------
TEST1 COMPTEST              TEST1        STU          3

查找不同的数据

select local_rowid,remote_rowid,status fromdba_comparison_row_dif where

comparison_name='COMPTEST';

LOCAL_ROWID       REMOTE_ROWID      STATUS
------------------ ------------------ ------
AAAS6OAAEAAAAK9AAA AAAS6IAAEAAAAKLAAB DIF
AAAS6OAAEAAAAK9AAC AAAS6IAAEAAAAKPAAC DIF
AAAS6OAAEAAAAK9AAD AAAS6IAAEAAAAKPAAD DIF

6.根据scan_id执行converge函数进行会聚

假设我们需要使用test2.stu的数据优先覆盖test1.stu的数据，也就是远程优先

declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge(comparison_name=>'COMPTEST',
scan_id=>24,
scan_info=>compare_info,
converge_options=>dbms_comparison.cmp_converge_remote_wins);

dbms_output.put_line('--- Results ---');
dbms_output.put_line('Local rows Merged by process: '||

compare_info.loc_rows_merged);
dbms_output.put_line('Remote rows Merged by process: '||

compare_info.rmt_rows_merged);
dbms_output.put_line('Local rows Deleted by process: '||

compare_info.loc_rows_deleted);
dbms_output.put_line('Remote rows Deleted by process: '||

compare_info.rmt_rows_deleted);
end;
/

执行结果：
— Results —
Local rows Merged by process: 3
Remote rows Merged by process: 0
Local rows Deleted by process: 3
Remote rows Deleted by process: 0
7.使用recheck函数重新执行比较

declare
compare_return boolean;
begin
compare_return := dbms_comparison.recheck

(comparison_name=>'COMPTEST',scan_id=>24);

if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is datadivergence.');
end if;
end;
/
由于已经同步，则比较结果是两个对象的数据是相同的，the tables are equivalent.

8.使用purge_comparison过程清除比较结果

EXEC dbms_comparison.purge_comparison('COMPTEST')
或者
begin
dbms_comparison.purge_comparison(comparison_name=>'COMPTEST');
end;
/

9.使用drop_comparison过程删除比较任务

EXEC dbms_comparison.drop_comparison('COMPTEST')
或者
begin
dbms_comparison.drop_comparison(comparison_name=>'COMPTEST');
end;
/

至此整个实验做完。

疑问：

至于两张表是否可以部分比较，还未做成功，也没有相关的文档。