dbms_comparison包测试

11G新特性比对数据库对象（DBMS_COMPARISON）
源端数据库版本必须是高于11.1，目标端数据库版本必须高于10.1
Oracle11g中引入了一个新的PL/SQL包DBMS_COMPARISON，可以比较不同数据库下或者schame下的对象数据，并且可以根据规则将不同的数据进行同步,但局限性较大，目前支持如下三种：
1.普通堆表
2.视图
3.物化视图
不支持如下column类型：long，long raw，rowid，urowid，clob，blob，nclob，bfile，User-defined types，Oracle-supplied types

例如你的数据复制过程中出现问题，导致源数据和目标数据出现不一致，则可以借用该特性进行处理。可以通过以下方法安装:
1、以sys身份登入数据库
2、执行$ORACLE_HOME/rdbms/admin/dbmscmp.sql

测试如下：
create user test1 identified by test;
create user test2 identified by test;
grant dba to test1,test2;

create table test1.t1(age number not null,name varchar2(20),id varchar2(5));
alter table test1.t1 add constraint pk_t1 primary key(age);
create table test2.t2(age number not null,name varchar2(20),id varchar2(5));
alter table test2.t2 add constraint pk_t2 primary key(age);

SQL> select * from test1.t1;

   AGE NAME 		ID

15 tom			a1
26 jack 		b2
37 jim			c3

SQL> select * from test2.t2;

   AGE NAME 		ID

15 tom			a1
26 jack 		b2
37 jim			c3
11 gx			a2
12 gxx			b2
13 gxxx 		c2
14 gxxxx		d2

利用create_comparison建立比较任务
begin
dbms_comparison.create_comparison(comparison_name=>‘COMPTEST’,
schema_name=>‘TEST1’,
object_name=>‘t1’,
dblink_name=>NULL,
remote_schema_name=>‘TEST2’,
remote_object_name=>‘t2’);
end;
/

查询基表数据字典和当前用户比对结果数据字典
SELECT COMPARISON_NAME,
COMPARISON_MODE,
SCHEMA_NAME,
OBJECT_NAME,
RMT_SCHEMA_NAME,
RMT_OBJECT_NAME
FROM SYS.COMPARISON$;

COMPARISON COMPARISON_MODE SCHEMA_NAME OBJECT_NAME RMT_SCHEMA_NAME RMT_OBJECT_NAME

COMPTEST 1 TEST1 T1 TEST2 T2

SELECT COMPARISON_NAME, COLUMN_POSITION, COLUMN_NAME, INDEX_COLUMN
FROM USER_COMPARISON_COLUMNS;
COMPARISON COLUMN_POSITION COLUMN_NAM I

COMPTEST 1 AGE Y
COMPTEST 2 NAME N
COMPTEST 3 ID N

执行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’,–上面步骤执行中的COMPARISON_NAME
scan_info=>compare_info,–PLSQL匿名快声明的变量名称
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:41

PL/SQL procedure successfully completed.

再次查询数据字典，验证比对结果和不同行源，切记上面的匿名快切勿重复执行，否则scan_id会叠加出现在DBA_COMPARISON_SCAN_SUMMARY数据字典中
SELECT A.OWNER,
A.COMPARISON_NAME,
A.SCHEMA_NAME,
A.OBJECT_NAME,
Z.scan_id,
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 = &SCAN_ID;

OWNER COMPARISON SCHEMA_NAME OBJECT_NAME SCAN_ID DIFFERENCE

SYS COMPTEST TEST1 T1 41 4

SELECT LOCAL_ROWID, REMOTE_ROWID, STATUS
FROM DBA_COMPARISON_ROW_DIF
WHERE COMPARISON_NAME = UPPER(’&COMPARISON_NAME’);

LOCAL_ROWID REMOTE_ROWID STA

	           AAAR9aAAHAAAACWAAD DIF
	           AAAR9aAAHAAAACWAAE DIF
	           AAAR9aAAHAAAACWAAF DIF
	           AAAR9aAAHAAAACWAAG DIF

基于上面匿名快比对操作，可以确定源端和远程端对应的scan_id和物理rowid地址，基于scan_id我们可以定位指定行源，我们只需要在做一步CONVERGE
操作即可将源端和目标端的物理表数据量完成同步操作。

根据scan_id执行converge函数进行会聚
假设我们需要使用test1.t1的数据优先覆盖test2.t2的数据
DECLARE
V_COMPARISON_INFO DBMS_COMPARISON.COMPARISON_TYPE;
BEGIN
DBMS_COMPARISON.CONVERGE(COMPARISON_NAME => ‘&COMPARISON_NAME’,
SCAN_ID => &SCAN_ID,
SCAN_INFO => V_COMPARISON_INFO,
CONVERGE_OPTIONS => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS);
DBMS_OUTPUT.PUT_LINE(’— Results —’);
DBMS_OUTPUT.PUT_LINE('Local rows Merged by process: ’ ||
V_COMPARISON_INFO.LOC_ROWS_MERGED);
DBMS_OUTPUT.PUT_LINE('Remote rows Merged by process: ’ ||
V_COMPARISON_INFO.RMT_ROWS_MERGED);
DBMS_OUTPUT.PUT_LINE('Local rows Deleted by process: ’ ||
V_COMPARISON_INFO.LOC_ROWS_DELETED);
DBMS_OUTPUT.PUT_LINE('Remote rows Deleted by process: ’ ||
V_COMPARISON_INFO.RMT_ROWS_DELETED);
END;
执行结果：
Enter value for comparison_name: COMPTEST
old 4: DBMS_COMPARISON.CONVERGE(COMPARISON_NAME => ‘&COMPARISON_NAME’,
new 4: DBMS_COMPARISON.CONVERGE(COMPARISON_NAME => ‘COMPTEST’,
Enter value for scan_id: 41
old 5: SCAN_ID => &SCAN_ID,
new 5: SCAN_ID => 41,
— Results —
Local rows Merged by process: 0
Remote rows Merged by process: 0
Local rows Deleted by process: 0
Remote rows Deleted by process: 4

PL/SQL procedure successfully completed.

ps:如果需要test2，覆盖test1远程优先则修改为如下即可
converge_options=>dbms_comparison.cmp_converge_remote_wins);

使用recheck函数重新执行比较
declare
compare_return boolean;
begin
compare_return := dbms_comparison.recheck
(comparison_name=>‘COMPTEST’,scan_id=>41);
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.

使用purge_comparison过程清除比较结果
EXEC dbms_comparison.purge_comparison(‘COMPTEST’)
或者
begin
dbms_comparison.purge_comparison(comparison_name=>‘COMPTEST’);
end;
/
使用drop_comparison过程删除比较任务
EXEC dbms_comparison.drop_comparison(‘COMPTEST’)
或者
begin
dbms_comparison.drop_comparison(comparison_name=>‘COMPTEST’);
end;
/
至此整个实验做完。