DBMS_COMPARISON
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.
- 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;
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;
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.
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
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;
--------------- ----------
---------------- ----------------- ----------
TEST_CMP_1 21 BUCKET DIF 5 10
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
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
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.
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
SQL> SELECT COUNT(*) FROM local_user.tab1;
COUNT(*)
----------
9
----------
9
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.
----------
5
----------
5
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
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';
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
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.
Source:
https://oracle-base.com/articles/11g/dbms_comparison-identify-row-differences-between-objects
Comments
Post a Comment