[20131211]对比两个表的不同信息.txt
昨天看了http://jonathanlewis.wordpress.com/2013/12/09/differences/博客，通过包dbms_rectifier_diff可以查看两个表存在那些不同。
自己做一个测试看看，重复测试。
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1.建立环境：
create table t1 nologging as
select rownum id, to_char(rownum) small_vc, rpad(rownum,100,'x') padding from all_objects where rownum
alter table t1 add constraint t1_pk primary key (id) nologging;
delete from t1 where id in (22, 300, 850);
update t1 set padding=rpad(rownum,100,'y') where id=1;
--我修改1条记录。
create table t2 nologging as
select rownum id, to_char(rownum) small_vc, rpad(rownum,100,'x') padding from all_objects where rownum
alter table t2 add constraint t2_pk primary key (id) nologging;
delete from t2 where id in (55, 475, 760);
commit;
drop table diff_data purge;
drop table diff_rowids purge;
create table diff_data ( id number, small_vc varchar2(40), padding varchar2(100));
create table diff_rowids( r_id rowid, present varchar2(4000), absent varchar2(4000)) ;
2.对比：
begin
dbms_rectifier_diff.differences(
sname1 => user, -- reference schema
oname1 => 'T1', -- reference table
reference_site => null, -- reference site (null => current) db_link
sname2 => user, -- target schema
oname2 => 'T2', -- target table
comparison_site => NULL, -- target site (null => current) db_link
where_clause => 'id
-- optional where clause to restrict rows
column_list => null, -- e.g. 'id,padding' , null=> all,
-- leave no spaces around commas
missing_rows_sname => user, -- output schema,
missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data
missing_rows_oname2 => 'DIFF_ROWIDS',-- table to locate mismatching data
missing_rows_site => null, -- output site (null => current) db_link
max_missing => 10, -- how many differences before stopping
commit_rows => 100 -- how many rows to commit in output
);
end;
/
--我修改了comparison_site=> NULL.
column absent format a30
column present format a30
SCOTT@test> select * from diff_rowids;
R_ID PRESENT ABSENT
------------------ ------------------------------ ------------------------------
AABEqAAAEAAAAJ8AAA TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAB TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAC TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAD TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAE TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAF TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAG TEST.COM TEST.COM
AABEqAAAEAAAAJ8AAH TEST.COM TEST.COM
8 rows selected.
column id format 999
column small_vc format a10
column padding format a10
SCOTT@test> select rowid, id, small_vc, substr(padding,1,10) padding from diff_data;
ROWID ID SMALL_VC PADDING
------------------ ---- ---------- ----------
AABEqAAAEAAAAJ8AAA 1 1 1yyyyyyyyy
AABEqAAAEAAAAJ8AAB 55 55 55xxxxxxxx
AABEqAAAEAAAAJ8AAC 475 475 475xxxxxxx
AABEqAAAEAAAAJ8AAD 760 760 760xxxxxxx
AABEqAAAEAAAAJ8AAE 1 1 1xxxxxxxxx
AABEqAAAEAAAAJ8AAF 22 22 22xxxxxxxx
AABEqAAAEAAAAJ8AAG 300 300 300xxxxxxx
AABEqAAAEAAAAJ8AAH 850 850 850xxxxxxx
8 rows selected.
--可以发现没有或者不同记录记录在diff_data;
3.除去不同的记录：
SCOTT@test> select count(*) from t1;
COUNT(*)
----------
997
SCOTT@test> select count(*) from t2;
COUNT(*)
----------
997
begin
dbms_rectifier_diff.RECTIFY(
sname1 => user, -- reference schema
oname1 => 'T1', -- reference table
reference_site => null, -- reference site (null => current)
sname2 => user, -- target schema
oname2 => 'T2', -- target table
comparison_site => NULL, -- target site (null => current)
column_list => null, -- e.g. 'id,padding' , null=> all,
-- leave no spaces around commas
missing_rows_sname => user, -- output schema,
missing_rows_oname1 => 'DIFF_DATA', -- table to list mismatching data
missing_rows_oname2 => 'DIFF_ROWIDS',-- table to locate mismatching data
missing_rows_site => null, -- output site (null => current)
commit_rows => 100 -- how many rows to commit in output
);
end;
/
--我修改了comparison_site=> NULL.
SCOTT@test> select * from diff_rowids;
no rows selected
SCOTT@test> select rowid, id, small_vc, substr(padding,1,10) padding from diff_data;
no rows selected
SCOTT@test> select count(*) from t1;
COUNT(*)
----------
997
SCOTT@test> select count(*) from t2;
COUNT(*)
----------
993
SCOTT@test> select * from t2 where id in (1,22, 300, 850);
no rows selected
--可以发现T2删除了id in (1,22, 300, 850)的记录。