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),
classno
varchar2(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 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预定义的类型)类型的列都还不能
进行较。
如果违反上述条件,欲比较的对象上缺乏所需要的索引的话,则会收到以下错误:
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 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;
/
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 from
dba_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 data
divergence.');
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;
/
至此整个实验做完。
疑问:
至于两张表是否可以部分比较,还未做成功,也没有相关的文档。