8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DBMS_COMPARISON : Identify Row Differences Between Objects and Optionally Converge the Data
The DBMS_COMPARISON
package makes it easy to identify row differences between tables, single-table views, materialized views and synonyms to the previous object types, and optionally converge the data.
The documentation includes security information, operational notes, indexing requirements and a number of restrictions associated with this functionality, which you can read here. In this article we will keep things simple and just do a basic comparison between two tables.
It's worth checking out this post by Stew Ashton about possible inaccuracies when using this method for comparing objects.
- Setup
- Create a New Comparison
- Run the Comparison
- Check the Results of the Comparison
- Recheck the Comparison
- Converge the Data in the Objects (Optional)
- Purge a Comparison
- Drop a Comparison
- Trigger Timing Points
Setup
Create two test users.
CONN / AS SYSDBA DROP USER local_user CASCADE; DROP USER remote_user CASCADE; CREATE USER local_user IDENTIFIED BY local_user QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO local_user; CREATE USER remote_user IDENTIFIED BY remote_user QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE VIEW, CREATE MATERIALIZED VIEW, CREATE SYNONYM TO remote_user;
The comparisons can be performed by the SYS user, or you can make the DBMS_COMPARISON
package available to the local user by granting execute on it, or by granting the EXECUTE_CATALOG_ROLE
role to the user.
GRANT EXECUTE ON DBMS_COMPARISON TO local_user; --GRANT EXECUTE_CATALOG_ROLE ON DBMS_COMPARISON TO local_user;
Create and populate some test tables.
DROP TABLE local_user.tab1 PURGE; CREATE TABLE local_user.tab1 ( id NUMBER NOT NULL, description VARCHAR2(50), created_date DATE, CONSTRAINT tab1_pk PRIMARY KEY (id) ); INSERT INTO local_user.tab1 SELECT level, 'Description for ' || level, TRUNC(SYSDATE) - level FROM dual CONNECT BY level <= 10; COMMIT; DROP TABLE remote_user.tab1 PURGE; CREATE TABLE remote_user.tab1 ( id NUMBER NOT NULL, description VARCHAR2(50), created_date DATE, CONSTRAINT tab1_pk PRIMARY KEY (id) ); INSERT INTO remote_user.tab1 SELECT level, 'Description for ' || level, TRUNC(SYSDATE) - level FROM dual CONNECT BY level <= 5; COMMIT;
The user running DBMS_COMPARISON
package must have the necessary privileges on the objects being compared, either directly or via a database link user.
GRANT SELECT, INSERT, UPDATE, DELETE ON remote_user.tab1 TO local_user;
Create a New Comparison
A new comparison is created using the CREATE_COMPARISON
procedure. There are a number parameters that affect the comparison, documented here, but the following simple example creates a comparison between two tables in separate schemas of the same database. The DBLINK_NAME
parameter could be used if the remote objects where in a separate database.
CONN local_user/local_user BEGIN DBMS_COMPARISON.create_comparison ( comparison_name => 'test_cmp_1', schema_name => 'local_user', object_name => 'tab1', dblink_name => NULL, remote_schema_name => 'remote_user', remote_object_name => 'tab1'); END; /
The default action is to compare the contents of all the columns in the objects, but a subset of columns can be compared using the COLUMN_LIST
parameter, which accepts a comma-separated list of column names.
Run the Comparison
The COMPARE
function runs one or more scans and returns FALSE if there are row differences between the local and remote objects. The process groups ranges of rows into buckets and compares them. If you set the PERFORM_ROW_DIF
parameter to TRUE it will do an additional row-difference check for buckets that showed a difference.
SET SERVEROUTPUT ON DECLARE l_scan_info DBMS_COMPARISON.comparison_type; l_result BOOLEAN; BEGIN l_result := DBMS_COMPARISON.compare ( comparison_name => 'test_cmp_1', scan_info => l_scan_info, perform_row_dif => TRUE ); IF NOT l_result THEN DBMS_OUTPUT.put_line('Differences found. scan_id=' || l_scan_info.scan_id); ELSE DBMS_OUTPUT.put_line('No differences found.'); END IF; END; / Differences found. scan_id=21 PL/SQL procedure successfully completed. SQL>
We can see the SCAN_ID
in the output above, or we can query it from the DBA_COMPARISON_SCAN
view. The comparison can make multiple passes, logging each subsequent scan as child of the parent scan. The top-most parent, or root scan, is the one with no PARENT_SCAN_ID
present.
SELECT scan_id FROM user_comparison_scan WHERE comparison_name = 'TEST_CMP_1' AND parent_scan_id IS NULL; SCAN_ID ---------- 21 SQL>
Check the Results of the Comparison
The following views are available to display information about comparisons and scan results. The CDB_%
views are only available in Oracle 12c to support multitenant environments.
ALL_COMPARISON_SCAN_SUMMARY
{USER|DBA|CDB}_COMPARISON
{USER|DBA|CDB}_COMPARISON_COLUMNS
{USER|DBA|CDB}_COMPARISON_ROW_DIF
{USER|DBA|CDB}_COMPARISON_SCAN
{USER|DBA|CDB}_COMPARISON_SCAN_SUMMARY
{USER|DBA|CDB}_COMPARISON_SCAN_VALUES
Some of these views are used below.
The USER_COMPARISON_SCAN_SUMMARY
view gives an overview of the comparison results.
SET LINESIZE 100 COLUMN comparison_name FORMAT A15 SELECT comparison_name, scan_id, status, current_dif_count, count_rows FROM user_comparison_scan_summary WHERE scan_id = 21; COMPARISON_NAME SCAN_ID STATUS CURRENT_DIF_COUNT COUNT_ROWS --------------- ---------- ---------------- ----------------- ---------- TEST_CMP_1 21 BUCKET DIF 5 10 SQL>
The USER_COMPARISON_COLUMNS
view lists the columns that were used in the comparison, and if they were part of the index used in the comparison.
COLUMN column_name FORMAT A20 SELECT comparison_name, column_position, column_name, index_column FROM user_comparison_columns WHERE comparison_name = 'TEST_CMP_1' ORDER BY column_position; COMPARISON_NAME COLUMN_POSITION COLUMN_NAME I --------------- --------------- -------------------- - TEST_CMP_1 1 ID Y TEST_CMP_1 2 DESCRIPTION N TEST_CMP_1 3 CREATED_DATE N SQL>
The USER_COMPARISON_ROW_DIF
view displays all the row differences. In the output below all the rows listed are present in the local database, but not in the remote database.
SELECT comparison_name, local_rowid, remote_rowid, status FROM user_comparison_row_dif WHERE comparison_name = 'TEST_CMP_1'; COMPARISON_NAME LOCAL_ROWID REMOTE_ROWID STA --------------- ------------------ ------------------ --- TEST_CMP_1 AAATwrAAMAAAADWAAF DIF TEST_CMP_1 AAATwrAAMAAAADWAAG DIF TEST_CMP_1 AAATwrAAMAAAADWAAH DIF TEST_CMP_1 AAATwrAAMAAAADWAAI DIF TEST_CMP_1 AAATwrAAMAAAADWAAJ DIF SQL>
Recheck the Comparison
The RECHECK
function checks a previously completed scan, or completes a previous partially completed scan. Remove one of the rows from the local table and perform a recheck.
DELETE FROM local_user.tab1 WHERE id = 10; COMMIT; SET SERVEROUTPUT ON DECLARE l_result BOOLEAN; BEGIN l_result := DBMS_COMPARISON.recheck ( comparison_name => 'test_cmp_1', scan_id => 21, perform_row_dif => TRUE ); IF NOT l_result THEN DBMS_OUTPUT.put_line('Differences found.'); ELSE DBMS_OUTPUT.put_line('No differences found.'); END IF; END; / Differences found. PL/SQL procedure successfully completed. SQL>
We can see the row deleted from the local table is no longer seen as different compared to the table in the remote user.
SELECT comparison_name, local_rowid, remote_rowid, status FROM user_comparison_row_dif WHERE comparison_name = 'TEST_CMP_1'; COMPARISON_NAME LOCAL_ROWID REMOTE_ROWID STA --------------- ------------------ ------------------ --- TEST_CMP_1 AAATwrAAMAAAADWAAF DIF TEST_CMP_1 AAATwrAAMAAAADWAAG DIF TEST_CMP_1 AAATwrAAMAAAADWAAH DIF TEST_CMP_1 AAATwrAAMAAAADWAAI DIF TEST_CMP_1 AAATwrAAMAAAADWAAJ SUC SQL>
Converge the Data in the Objects (Optional)
We can converge the data in the two tables. That is, make them match. By default a CONVERGE
assumes the remote table should be made to look like the local table, as shown in the example below, which doesn't commit the change.
SET SERVEROUTPUT ON DECLARE l_scan_info DBMS_COMPARISON.comparison_type; l_result BOOLEAN; BEGIN DBMS_COMPARISON.converge ( comparison_name => 'test_cmp_1', scan_id => 21, scan_info => l_scan_info, converge_options => DBMS_COMPARISON.cmp_converge_local_wins, -- Default perform_commit => FALSE ); DBMS_OUTPUT.put_line('scan_id = ' || l_scan_info.scan_id); DBMS_OUTPUT.put_line('loc_rows_merged = ' || l_scan_info.loc_rows_merged); DBMS_OUTPUT.put_line('rmt_rows_merged = ' || l_scan_info.rmt_rows_merged); DBMS_OUTPUT.put_line('loc_rows_deleted = ' || l_scan_info.loc_rows_deleted); DBMS_OUTPUT.put_line('rmt_rows_deleted = ' || l_scan_info.rmt_rows_deleted); END; / scan_id = 21 loc_rows_merged = 0 rmt_rows_merged = 4 loc_rows_deleted = 0 rmt_rows_deleted = 0 PL/SQL procedure successfully completed. SQL> SELECT COUNT(*) FROM local_user.tab1; COUNT(*) ---------- 9 SQL> SELECT COUNT(*) FROM remote_user.tab1; COUNT(*) ---------- 9 SQL>
We can rollback the change and repeat it, this time making the local table match the remote table and committing the change.
ROLLBACK; SET SERVEROUTPUT ON DECLARE l_scan_info DBMS_COMPARISON.comparison_type; l_result BOOLEAN; BEGIN DBMS_COMPARISON.converge ( comparison_name => 'test_cmp_1', scan_id => 21, scan_info => l_scan_info, converge_options => DBMS_COMPARISON.cmp_converge_remote_wins, perform_commit => TRUE ); DBMS_OUTPUT.put_line('scan_id = ' || l_scan_info.scan_id); DBMS_OUTPUT.put_line('loc_rows_merged = ' || l_scan_info.loc_rows_merged); DBMS_OUTPUT.put_line('rmt_rows_merged = ' || l_scan_info.rmt_rows_merged); DBMS_OUTPUT.put_line('loc_rows_deleted = ' || l_scan_info.loc_rows_deleted); DBMS_OUTPUT.put_line('rmt_rows_deleted = ' || l_scan_info.rmt_rows_deleted); END; / scan_id = 21 loc_rows_merged = 0 rmt_rows_merged = 0 loc_rows_deleted = 4 rmt_rows_deleted = 0 PL/SQL procedure successfully completed. SQL> SELECT COUNT(*) FROM local_user.tab1; COUNT(*) ---------- 5 SQL> SELECT COUNT(*) FROM remote_user.tab1; COUNT(*) ---------- 5 SQL>
Purge a Comparison
The PURGE_COMPARISON
procedure removes all information about scans for the comparison, so they can be run in full again.
BEGIN DBMS_COMPARISON.purge_comparison( comparison_name => 'test_cmp_1', scan_id => 21); END; / SELECT scan_id FROM user_comparison_scan WHERE comparison_name = 'TEST_CMP_1' AND parent_scan_id IS NULL; no rows selected SQL>
Drop a Comparison
The DROP_COMPARISON
procedure removes all information about the specified comparison.
BEGIN DBMS_COMPARISON.drop_comparison( comparison_name => 'test_cmp_1'); END; / SELECT comparison_name FROM user_comparison WHERE comparison_name = 'TEST_CMP_1'; no rows selected SQL>
Triggers Timing Points
If you are interesting in seeing the trigger timing points associated with this functionality, create the following additional objects in the local and remote users.
CONN / AS SYSDBA -- Drop history objects. DROP SEQUENCE local_user.tab1_history_seq; DROP TABLE local_user.tab1_history PURGE; DROP SEQUENCE remote_user.tab1_history_seq; DROP TABLE remote_user.tab1_history PURGE; -- Create sequences and history tables. CREATE SEQUENCE local_user.tab1_history_seq; CREATE SEQUENCE remote_user.tab1_history_seq; CREATE TABLE local_user.tab1_history ( id NUMBER, action VARCHAR2(100) ); CREATE TABLE remote_user.tab1_history ( id NUMBER, action VARCHAR2(100) ); GRANT SELECT ON remote_user.tab1_history TO local_user; -- Create compound triggers to track changes. CREATE OR REPLACE TRIGGER local_user.tab1_trg FOR INSERT OR UPDATE OR DELETE ON local_user.tab1 COMPOUND TRIGGER g_action tab1_history.action%TYPE; BEFORE STATEMENT IS BEGIN CASE WHEN INSERTING THEN g_action := 'BSI'; WHEN UPDATING THEN g_action := 'BSU'; WHEN DELETING THEN g_action := 'BSD'; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN CASE WHEN INSERTING THEN g_action := 'BRI ' || :new.id || ':' || :new.description || ':' || :new.created_date; WHEN UPDATING THEN g_action := 'BRU ' || :old.id || ':' || :old.description || ':' || :old.created_date; WHEN DELETING THEN g_action := 'BRD ' || :old.id || ':' || :old.description || ':' || :old.created_date; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN CASE WHEN INSERTING THEN g_action := 'ARI ' || :new.id || ':' || :new.description || ':' || :new.created_date; WHEN UPDATING THEN g_action := 'ARU ' || :old.id || ':' || :old.description || ':' || :old.created_date; WHEN DELETING THEN g_action := 'ARD ' || :old.id || ':' || :old.description || ':' || :old.created_date; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END AFTER EACH ROW; AFTER STATEMENT IS BEGIN CASE WHEN INSERTING THEN g_action := 'ASI'; WHEN UPDATING THEN g_action := 'ASU'; WHEN DELETING THEN g_action := 'ASD'; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END AFTER STATEMENT; END tab1_trg; / CREATE OR REPLACE TRIGGER remote_user.tab1_trg FOR INSERT OR UPDATE OR DELETE ON remote_user.tab1 COMPOUND TRIGGER g_action tab1_history.action%TYPE; BEFORE STATEMENT IS BEGIN CASE WHEN INSERTING THEN g_action := 'BSI'; WHEN UPDATING THEN g_action := 'BSU'; WHEN DELETING THEN g_action := 'BSD'; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END BEFORE STATEMENT; BEFORE EACH ROW IS BEGIN CASE WHEN INSERTING THEN g_action := 'BRI ' || :new.id || ':' || :new.description || ':' || :new.created_date; WHEN UPDATING THEN g_action := 'BRU ' || :old.id || ':' || :old.description || ':' || :old.created_date; WHEN DELETING THEN g_action := 'BRD ' || :old.id || ':' || :old.description || ':' || :old.created_date; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN CASE WHEN INSERTING THEN g_action := 'ARI ' || :new.id || ':' || :new.description || ':' || :new.created_date; WHEN UPDATING THEN g_action := 'ARU ' || :old.id || ':' || :old.description || ':' || :old.created_date; WHEN DELETING THEN g_action := 'ARD ' || :old.id || ':' || :old.description || ':' || :old.created_date; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END AFTER EACH ROW; AFTER STATEMENT IS BEGIN CASE WHEN INSERTING THEN g_action := 'ASI'; WHEN UPDATING THEN g_action := 'ASU'; WHEN DELETING THEN g_action := 'ASD'; END CASE; INSERT INTO tab1_history (id, action) VALUES (tab1_history_seq.NEXTVAL, g_action); END AFTER STATEMENT; END tab1_trg; /
Work through the previous examples up to the first converge command to copy the missing rows to the table in the remote user. Remembering to adjust the SCAN_ID
values where necessary. Check the history records in the remote user after running the first converge command.
COLUMN action FORMAT A50 SELECT id, action FROM remote_user.tab1_history ORDER BY 1; ID ACTION ---------- -------------------------------------------------- 1 BSI 2 BSU 3 BRI 6:Description for 6:10/12/2017 00:00:00 4 BRI 7:Description for 7:10/11/2017 00:00:00 5 BRI 8:Description for 8:10/10/2017 00:00:00 6 BRI 9:Description for 9:10/09/2017 00:00:00 7 ARI 6:Description for 6:10/12/2017 00:00:00 8 ARI 7:Description for 7:10/11/2017 00:00:00 9 ARI 8:Description for 8:10/10/2017 00:00:00 10 ARI 9:Description for 9:10/09/2017 00:00:00 11 ASI ID ACTION ---------- -------------------------------------------------- 12 ASU 13 BSD 14 ASD 14 rows selected. SQL>
Move on to the second converge command and check the history records in the local user.
COLUMN action FORMAT A50 SELECT id, action FROM local_user.tab1_history ORDER BY 1; ID ACTION ---------- -------------------------------------------------- 1 BSD 2 BRD 10:Description for 10:08-OCT-17 3 ARD 10:Description for 10:08-OCT-17 4 ASD 5 BSI 6 BSU 7 ASI 8 ASU 9 BSD 10 BRD 6:Description for 6:10/12/2017 00:00:00 11 ARD 6:Description for 6:10/12/2017 00:00:00 ID ACTION ---------- -------------------------------------------------- 12 BRD 7:Description for 7:10/11/2017 00:00:00 13 ARD 7:Description for 7:10/11/2017 00:00:00 14 BRD 8:Description for 8:10/10/2017 00:00:00 15 ARD 8:Description for 8:10/10/2017 00:00:00 16 BRD 9:Description for 9:10/09/2017 00:00:00 17 ARD 9:Description for 9:10/09/2017 00:00:00 18 ASD 18 rows selected. SQL>
For more information see:
Hope this helps. Regards Tim...