11g New Feature - DBMS_COMPARISON!!

I thought I'd share an 11g New Feature with you today! This is in the form of the dbms_comparison package that is new in Oracle Database 11g.

With dbms_compare you can compare objects/schemas/data between two different databases or schema's. This is handy, say, in replicated environments when things get out of wack. You can use dbms_compare to help determine if there is some data divergence between two tables. If data divergence is found you can bring the objects to a consistent state.

For example, if I have two schemas in my database (scott and Robert in this case) I can compare the EMP tables in both schemas. First I create the comparison:

exec dbms_comparison.create_comparison('robert','robert','emp',null,null,null,'scott','emp');

This tells Oracle that I'm getting ready to compare these two objects. You can do this over a DBLink too by just defining the name of the dblink. Even more, while the local compare side must be Oracle Database 11g, the remote site can be Oracle Database 10g release one or later!!

Now I can do the actual compare as seen here:

declare
compare_info dbms_comparison.comparison_type;
compare_return boolean;
begin
compare_return :=
dbms_comparison.compare (
comparison_name=>'robert',
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;
/

Here is the result in my case:

Bad news... there is data divergence.
Check the dba_comparison and dba_comparison_scan_summary views for locate the
differences for scan_id:13

As we can see here, the results send us to dba_comparison and dba_comparison_scan_summary where we find these results:

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=13;

OWNER COMPARISON_NAME
------------------------------ ------------------------------
SCHEMA_NAME OBJECT_NAME DIFFERENCE
------------------------------ ------------------------------ ----------
ROBERT ROBERT
ROBERT EMP 16

There are actually a lot of views you can use here including:

• 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

You can also use the dbms_comparison.converge function to "fix" the data, as it were. This procedure will converge our objects, taking care of the data divergence. With this procedure you can either say that the remote or local table is to be the "master" table. Divergences will be sourced from that object. So, in our case we can update the robert.emp table so that it's no longer divergent with the scott.emp table. Here is an example:

declare
compare_info dbms_comparison.comparison_type;
begin
dbms_comparison.converge (
comparison_name=>'robert',
scan_id=>13,
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: 16
Remote rows Merged by process: 0
Local rows Deleted by process: 0
Remote rows Deleted by process: 0

Note that if you run this more than once, you are in no danger of duplicating your data as seen in this subsequent output from a second run:

--- Results ---
Local rows Merged by process: 0
Remote rows Merged by process: 0
Local rows Deleted by process: 0
Remote rows Deleted by process: 0

Something else to be aware of. If the data changes in either table, it can impact the compare/converge operations. Thus for best results, you should quiesce all activity on the tables being compared.

There are some other requirements with this feature that you will want to review (as always), but it is a very powerful new feature in Oracle Database 11g!

Fun fun 11g!!