When two copies of a table drift apart, in two databases or two schemas, DBMS_COMPARISON finds the differences row by row and CONVERGE fixes them. A comparison is defined once, COMPARE scans both tables and records the differing rows, and CONVERGE merges and deletes rows until one side matches the other.
Code for This Guide
The main examples are in the examples/pkg-data-ops folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_comparison.create_comparison(comparison_name, schema_name, object_name,
dblink_name, remote_schema_name, remote_object_name);
same := dbms_comparison.compare(comparison_name, scan_info_out,
perform_row_dif => true);
dbms_comparison.converge(comparison_name, scan_id, result_out,
converge_options => dbms_comparison.cmp_converge_local_wins);The local and remote objects need a primary key or another index on a NOT NULL column to match rows. Usually the remote table is in another database, reached through DBLINK_NAME.
Find the Differences
ROUTES_COPY starts as a copy of ROUTES; then two rows change and one is deleted.
Example:
create table routes_copy as select * from routes;
alter table routes_copy add primary key (route_id);
update routes_copy set block_minutes = block_minutes + 5 where route_id in (3, 7);
delete from routes_copy where route_id = 12;
commit;
declare
v_scan dbms_comparison.comparison_type;
v_same boolean;
begin
dbms_comparison.create_comparison(
comparison_name => 'ROUTES_CMP',
schema_name => user, object_name => 'ROUTES',
dblink_name => null, -- the other table is local too
remote_schema_name => user, remote_object_name => 'ROUTES_COPY');
v_same := dbms_comparison.compare('ROUTES_CMP', v_scan, perform_row_dif => true);
dbms_output.put_line('identical? ' || case when v_same then 'yes' else 'no' end);
end;
/
select index_value as route_id, local_rowid is not null as in_routes,
remote_rowid is not null as in_copy, status
from user_comparison_row_dif
where comparison_name = 'ROUTES_CMP'
order by to_number(index_value);Output:
Table ROUTES_COPY created. Table ROUTES_COPY altered. 2 rows updated. 1 row deleted. Commit complete. identical? no PL/SQL procedure successfully completed. ROUTE_ID IN_ROUTES IN_COPY STATUS ___________ ____________ __________ _________ 3 true true DIF 7 true true DIF 12 true false DIF
COMPARE returns FALSE, and USER_COMPARISON_ROW_DIF lists the three differences: routes 3 and 7 exist on both sides with different values, and route 12 is missing from the copy.
Converge the Copy
Example:
declare
v_scan dbms_comparison.comparison_type;
v_result dbms_comparison.comparison_type;
v_same boolean;
begin
v_same := dbms_comparison.compare('ROUTES_CMP', v_scan, perform_row_dif => true);
-- make the copy (the "remote" object) the same as the original
dbms_comparison.converge('ROUTES_CMP', v_scan.scan_id, v_result,
converge_options => dbms_comparison.cmp_converge_local_wins);
commit;
dbms_output.put_line('merged ' || v_result.rmt_rows_merged || ', deleted '
|| v_result.rmt_rows_deleted);
v_same := dbms_comparison.compare('ROUTES_CMP', v_scan);
dbms_output.put_line('identical now? ' || case when v_same then 'yes' end);
end;
/Output:
merged 3, deleted 0 identical now? yes PL/SQL procedure successfully completed.
With LOCAL_WINS, the original is the source of truth: three rows are merged into the copy, the two changed ones and the missing one. A second COMPARE confirms the tables are identical. The example drops the comparison and the copy afterward.
Things to Know
- CMP_CONVERGE_REMOTE_WINS makes the local table match the remote one instead.
- CONVERGE changes rows with normal DML; commit to keep the result.
- COMPARE can check only some columns or a range of rows, for large tables.
Related Guides
Conclusion
DBMS_COMPARISON compares two copies of a table row by row and CONVERGE makes one match the other. Define the comparison once, compare to see the differences, and converge in the direction you choose.
