dbms_compare can compare objects/schemas/data between two different databases or schema's. You can use it 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 HR in this case) I can compare the EMP tables in both schemas. First I create the comparison:
This tells Oracle that I'm getting ready to compare these two objects. This can be done 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 late.
Now I can do the actual compare as seen here:
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:
There are actually a lot of views you can use here including:
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 hr.emp table so that it's no longer divergent with the scott.emp table. Here is an example:
--- Results ---
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 ---
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!