Compare and Fix the Object Data in Oracle 11g:

Compare and Fix the Object Data in Oracle 11g:


The DBMS_COMPARISON package provides interfaces to compare and sync database objects at different databases. The index columns in a comparison must uniquely identify every row involved in a comparison.

The following constraints satisfy this requirement:

- A primary key constraint
- A unique constraint on one or more non-NULL columns

If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.


Simple Test:
drop table test.x PURGE;
drop table test2.x PURGE;

create table test.x (eno number(10), ename varchar2(20), sal number(10));
create table test2.x (eno number(10), ename varchar2(20), sal number(10));

alter table test.x add constraint x_pk primary key (eno);
alter table test2.x add constraint x_pk primary key (eno);


insert into test.x values (100, 'VJ', 100000);
insert into test.x values (101, 'ASHRAY', 100001);
insert into test.x values (102, 'DUMPA', 200000);
insert into test.x values (103, 'REDDY', 300000);
commit;

insert into test2.x values (100, 'VJ', 100000);
insert into test2.x values (101, 'ASHRAY', 100000);
insert into test2.x values (102, 'DUMPA', 200001);
insert into test2.x values (103, 'REDDY', 300000);
commit;

select * from test.x;
select * from test2.x;


-- Drop the existing comparison:

SELECT comparison_name, schema_name FROM dba_comparison;

BEGIN
dbms_comparison.drop_comparison (comparison_name => 'comp1');
END;
/


-- Create the comparison:
-- dblink_name is NULL because both schemas on the same database.

BEGIN
dbms_comparison.create_comparison (comparison_name => 'comp1',
schema_name => 'test',
object_name => 'x',
dblink_name => NULL,
remote_schema_name => 'test2',
remote_object_name => 'x'
);
END;
/


-- Check the difference "YES/NO":

set serveroutput on size 99999;

DECLARE
CONSISTENT BOOLEAN;
scan_info dbms_comparison.comparison_type;
BEGIN
CONSISTENT :=
dbms_comparison.compare (comparison_name => 'comp1',
scan_info => scan_info,
perform_row_dif => TRUE
);
DBMS_OUTPUT.put_line ('Scan ID: ' || scan_info.scan_id);

IF CONSISTENT = TRUE
THEN
DBMS_OUTPUT.put_line ('No differences were found.');
ELSE
DBMS_OUTPUT.put_line ('Differences were found.');
END IF;
END;
/
Scan ID: 11
Differences were found.

PL/SQL procedure successfully completed.


-- Comparison Summary:
-- Where scan_id is from the above dbms_comparison.compare PL/SQL block.

set linesize 120;
COL schema_name for a20;
COL object_name for a20;
COL comparison_name for a20;

SELECT s.scan_id, c.comparison_name, c.schema_name, c.object_name,
s.current_dif_count
FROM user_comparison c, user_comparison_scan_summary s
WHERE c.comparison_name = s.comparison_name AND s.scan_id = 11;


-- Comparison Details:

set linesize 120;
COL record_value for a50;
COL local_rowid for a12;
COL remote_rowid format a12;

SELECT c.column_name, r.index_value record_value,
CASE
WHEN r.local_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END local_rowid,
CASE
WHEN r.remote_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END remote_rowid
FROM dba_comparison_columns c,
dba_comparison_row_dif r,
dba_comparison_scan s
WHERE c.comparison_name = 'COMP1'
AND r.scan_id = s.scan_id
-- AND s.last_update_time > SYSTIMESTAMP - 1 / 24 / 12 -- Last 5 min.
AND r.status = 'DIF'
AND c.index_column = 'Y'
AND c.comparison_name = r.comparison_name
ORDER BY r.index_value;


-- Fix the difference in the remote table:

DECLARE
scan_info dbms_comparison.comparison_type;
BEGIN
dbms_comparison.converge
(comparison_name => 'comp1',
scan_id => 11,
scan_info => scan_info,
converge_options => dbms_comparison.cmp_converge_local_wins
);
DBMS_OUTPUT.put_line ('Local Rows Merged: ' || scan_info.loc_rows_merged);
DBMS_OUTPUT.put_line ('Remote Rows Merged: ' || scan_info.rmt_rows_merged);
DBMS_OUTPUT.put_line ('Local Rows Deleted: ' || scan_info.loc_rows_deleted);
DBMS_OUTPUT.put_line ('Remote Rows Deleted: ' || scan_info.rmt_rows_deleted);
END;
/
Local Rows Merged: 0
Remote Rows Merged: 2
Local Rows Deleted: 0
Remote Rows Deleted: 0

PL/SQL procedure successfully completed.


Results:

Before:
select * from test.x;
select * from test2.x;

SQL> select * from test.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000

SQL> select * from test2.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100000 <=== Old
102 DUMPA 200001 <=== Old
103 REDDY 300000


After:
select * from test.x;
select * from test2.x;

SQL> select * from test.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000

SQL> select * from test2.x;

ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001 <=== New
102 DUMPA 200000 <=== New
103 REDDY 300000

SQL>

Q:
a.
Can I fix the CLOB/BLOB column data?

No, the DBMS_COMPARISON package cannot compare data in columns of the following data types:

- LONG, LONG RAW, ROWID, UROWID, CLOB, NCLOB, BLOB and BFILE
- User-defined types (including object types, REFs, varrays, and nested tables)
- Oracle-supplied types (including any types, XML types, spatial types, and media types)


b.
Can I fix the object data in 11g by comparing the object in 10g?

Yes, The local database that runs the DBMS_COMPARISON package must be 11g but the remote database must be 10gR1 and up.


c.
Can I compare packages/procedure/functions?

No, the DBMS_COMPARISON package can compare the following types of database objects:

Tables, Single-table views, Materialized views, Synonyms for tables, single-table views and materialized views.


d.
Do I need to have primary key on compare object?

No, but each column in the single-column/composite index must either have a NOT NULL constraint or be part of the primary key.


e.
What privileges do I need to compare the objects?

Granting EXECUTE on CREATE_COMPARISON package to selected users or roles. (or) Granting EXECUTE_CATALOG_ROLE to selected users or roles.