作者：江枫 | 【转载时请务必以超链接形式标明文章原始出处和作者信息及本声明】
地址：
http://www.taobaodba.com/html/12_oracle11g_dbms_comparison.html
Oracle11g中引入了一个新的PL/SQL包DBMS_COMPARISON，可以比较不同数据库下或者schame下的对象/schema/数据，并且可以根据规则将不同的数据进行同步。这在进行数据的分布时将十分的有用，例如你的数据复制过程中出现问题，导致源数据和目标数据出现不一致，则可以借用该特性进行处理。如果你的系统中没有安装该过程，可以通过以下脚本手动安装：
$ORACLE_HOME/rdbms/admin/dbmscmp.sql
可以利用DBMS_COMPARISON来执行比较的对象有：
* 表
* 基于单个表的视图
* 物化视图
* 以上三种对象的同义词
下面通过一个简单的试验来演示这个包的使用。首先创建需要比较的两个测试表，为了简单起见，这里在同一个库的两个不同的schame中创建了两个基本相同的表(只有一个列的长度不一样)，表中数据不一致。
create table test1.test(i int,a varchar2(30));
create table test2.test(i int,a varchar2(20));
create index test1.ix_test on test1.test(i);
create index test2.ix_test on test2.test(i);
insert into test1.test values(1,'a');
insert into test1.test values(2,'b');
insert into test2.test values(1,'a');
insert into test2.test values(2,'c');
commit;
1.使用create_comparison过程创建一个比较任务
begin
dbms_comparison.create_comparison(comparison_name=>'COMPTEST',
schema_name=>'TEST1',
object_name=>'TEST',
dblink_name=>NULL,
remote_schema_name=>'TEST2',
remote_object_name=>'TEST');
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 LOCAL TIME ZONE)，或者Interval类型(INTERVAL YEAR TO MONTH | INTERVAL DAY TO SECOND)或者DATE类型的单列索引。
* 只包含上述类型列的复合索引，并且其中每个列要么有NOT NULL约束，要么是主键的一部分。
如果比较时的扫描模式选择的是CMP_SCAN_MODE_FULL或者CMP_SCAN_MODE_CUSTOM，索引可以放宽条件，除了上面说的列类型，也可以包含VARCHAR2或者CHAR列。
目前还只能支持常规列类型的比较，对于LONG | LONG RAW | ROWID | UROWID | CLOB | NCLOB | BLOB | BFILE | TYPE(包括用户自定义和Oracle预定义的类型)类型的列都还不能进行较。
可以看到，限制条件相当的多，对于新引进的这个特性，Oracle的支持还不是十分的到位，估计这个功能要达到实用，还有相当长的一段路要走。
如果违反上述条件，欲比较的对象上缺乏所需要的索引的话，则会收到以下错误：
ORA-23626: 表 TEST1.TEST 上没有符合要求的索引
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 4197
ORA-06512: 在 “SYS.DBMS_COMPARISON”, line 420
ORA-06512: 在 line 2
2.执行compare过程进行比较
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 data divergence.');
dbms_output.put_line('Check the dba_comparison and dba_comparison_scan_summary views for locate the differences for scan_id:'||compare_info.scan_id);
end if;
end;
/
执行的结果：
Bad news… there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the
differences for scan_id:14
3.查询以下视图获得比较结果
* 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
根据第二步得到的scan_id，我们来看看比较的结果：
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=14;
OWNER COMPARISON_NAME SCHEMA_NAME OBJECT_NAME DIFFERENCE
----- --------------- ------------- ------------- ---------
SYS COMPTEST TEST1 TEST 2
查找不同的数据
select local_rowid,remote_rowid,status from dba_comparison_row_dif where comparison_name='COMPTEST';
LOCAL_ROWID REMOTE_ROWID STA
------------------ ------------------ ---
AAADYbAAEAAAABOAAB DIF
AAADYdAAEAAAABWAAB DIF
4.如果有需要，执行converge过程进行同步
假设我们需要使用test2.test的数据优先覆盖test1.test的数据，也就是远程优先
declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge (comparison_name=>'COMPTEST',
scan_id=>14,
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: 1
Remote rows Merged by process: 0
Local rows Deleted by process: 1
Remote rows Deleted by process: 0
SQL> select * from test1.test;
I A
---------- --------------------
1 a
2 c
SQL> select * from test2.test;
I A
---------- --------------------
1 a
2 c
同步仅限于数据的同步，两个表的列长还是保持原来的定义：
SQL> desc test1.test
名称 是否为空? 类型
---------------------- ------- ----------------------------
I NUMBER(38)
A VARCHAR2(30)
SQL> desc test2.test
名称 是否为空? 类型
---------------------- -------- ----------------------------
I NUMBER(38)
A VARCHAR2(20)
5.使用recheck函数重新执行比较
declare
compare_return boolean;
begin
compare_return := dbms_comparison.recheck(comparison_name=>'COMPTEST',scan_id=>14);
if compare_return=TRUE
then
dbms_output.put_line('the tables are equivalent.');
else
dbms_output.put_line('Bad news... there is data divergence.');
end if;
end;
/
由于已经同步，则比较结果是两个对象的数据是相同的，the tables are equivalent.
6.使用purge_comparison过程清除比较结果
begin
dbms_comparison.purge_comparison(comparison_name=>'COMPTEST');
end;
/
7.使用drop_comparison过程删除比较任务
begin
dbms_comparison.drop_comparison(comparison_name=>'COMPTEST');
end;
/
无可否认，Oracle11g新引入的这个包功能相当的强大，但也有相当大的限制，而且对于大数据量的处理时的性能也有待更多的测试。本文只是列举了一个最简单的例子。想更多的了解这个包的用法和各种限制，请参考官方文档。
参考文章：
Robert G. Freeman’s Blog：11g New Feature - DBMS_COMPARISON!!
Oracle® Database PL/SQL Packages and Types Reference 11g Release 1 (11.1)
