8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 11g » Here

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.

Compare Tables

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 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.

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...

Back to the Top.