8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | 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...