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.
Tuesday, May 25, 2010
Subscribe to:
Post Comments (Atom)
3 comments:
If i have to converge the clob colums. is there any work around
I need to keep the existing values in the second table, Instead of merging. Is there any workaround?
Thanks
you can also try dbForge Data/Schema Compare for Oracle for data and schema comparison and synchronization with many options and scheduling features.
Post a Comment