Another interesting feature for Oracle 11g
is DBMS_COMPARISON package which planned to use compare data within
two schemas or between database`s schema. You can use this package
for several reason: if replication/streams broken, or checking between
production and test databases data etc.
To use DBMS_COMPARISON you have to grant. If you
connected as SYSDBA you already have had grant to execute. Other users
need execute grants on DBMS_COMPARISON and that user will need to be granted
execute_catalog_role too.
Note: The
database character sets must be the same for the databases that contain the
database objects being compared.
For all scan modes to be supported by
the DBMS_COMPARISON package, the database objects must have one of
the following types of indexes:
· A
single-column index on a number, timestamp, interval,
or DATE datatype column
· A
composite index that only includes number, timestamp, interval,
or DATE datatype columns. Each column in the composite index must
either
have a NOT NULL constraint or
be part of the primary key.
For the scan
modes CMP_SCAN_MODE_FULL and CMP_SCAN_MODE_CUSTOM to be
supported, the database objects must have one of the following types of
indexes:
· A
single-column index on a number, timestamp, interval, DATE, VARCHAR2,
or CHAR datatype column
· A
composite index that only includes number, timestamp,
interval, DATE, VARCHAR2, or CHAR columns. Each column in
the composite index must either have a NOT NULL constraint or be
part of the primary key.
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
Database objects of different types can be compared and
converged at different databases. For example, a table at one database and a
materialized view at another database can be compared and converged with this
package.
The DBMS_COMPARISON package supports all Oracle Data Types
(VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP
etc) except the following:
LONG
LONG RAW
ROWID
UROWID
CLOB
NCLOB
BLOB
BFILE
There are useful data dictionary views:
· DBA/USER_COMPARISON_SCAN
· DBA/USER_COMPARISON_SCAN_SUMMARY
· DBA/USER_COMPARISON_SCAN_VALUES
· DBA/USER_COMPARISON_ROW_DIF
Let`s check.
[oracle@localhost admin]$ export ORACLE_SID=testdb
[oracle@localhost admin]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 13
17:22:59 2012
Copyright (c) 1982, 2009, Oracle. All rights
reserved.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size
1336960 bytes
Variable Size
285215104 bytes
Database Buffers
130023424 bytes
Redo Buffers
6094848 bytes
Database mounted.
Database opened.
SQL> conn ulfet/ulfet
Connected.
--Created clone of hr.employees table without data
SQL> create table ulfet.emp_clone as select * from
hr.employees where 1=2;
Table created.
SQL>
--Hr.employees`s table has 107 rows
SQL> select count(1) from hr.employees;
COUNT(1)
----------
107
SQL>
--Now to synchronize the data:
SQL> set serveroutput on
DECLARE
scan_info
dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name
=> 'comp_emp');
dbms_comparison.create_comparison(comparison_name
=> 'comp_emp',
schema_name => 'ulfet',
object_name => 'emp_clone',
dblink_name =>
null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);
consistent := dbms_comparison.compare(comparison_name =>
'comp_emp', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No
differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name =>
'comp_emp',
scan_id =>
scan_info.scan_id,
scan_info => scan_info,
converge_options =>
dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
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;
/SQL> 2 3
4 5 6 7 8
9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24
25 26 27 28 29 30 31 32
33
DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table
ULFET.EMP_CLONE
ORA-06512: at
"SYS.DBMS_COMPARISON", line 4793
ORA-06512: at
"SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 7
--That you do not have an index on source
table, Oracle compares the data by index let`s create it
SQL> alter table emp_clone add constraint emp_pk primary
key (employee_id);
Table altered.
--Try again
SQL> set serveroutput on
DECLARE
scan_info
dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
-- dbms_comparison.drop_comparison(comparison_name
=> 'comp_emp');
dbms_comparison.create_comparison(comparison_name
=> 'comp_emp',
schema_name =>
'ulfet',
object_name =>
'emp_clone',
dblink_name => null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);
consistent := dbms_comparison.compare(comparison_name =>
'comp_emp', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No
differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name =>
'comp_emp',
scan_id
=> scan_info.scan_id,
scan_info =>
scan_info,
converge_options =>
dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
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;
/SQL> 2 3 4
5 6 7 8 9 10
11 12 13 14 15 16 17 18
19 20 21 22 23 24 25 26
27 28 29 30 31 32 33
Differences were found.
Scan ID: 13
Local Rows Merged:107
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0
PL/SQL procedure successfully completed.
SQL>
Now, we can check result:
SQL> select count(1) from emp_clone;
COUNT(1)
----------
107
SQL> select count(1) from hr.employees;
COUNT(1)
----------
107
SQL>
SQL> column a.owner format a6
column comp_name format a10
column schema format a6
column object format a12
select a.owner, a.comparison_name comp_name , a.schema_name
schema, a.object_name object, z.current_dif_count different_count
from dba_comparison a, dba_comparison_scan_summary z
where a.comparison_name=z.comparison_name
and a.owner=z.owner;
OWNER
COMP_NAME SCHEMA OBJECT
DIFFERENT_COUNT
------------------------------ ---------- ------
------------ ---------------
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
ULFET
COMP_EMP ULFET EMP_CLONE
0
OWNER
COMP_NAME SCHEMA OBJECT
DIFFERENT_COUNT
------------------------------ ---------- ------
------------ ---------------
ULFET
COMP_EMP ULFET EMP_CLONE
0
12 rows selected.
Now, change some data on emp_clone table and repeat above
action. This time different will be on emp_clone table not hr.employees.
SQL> show user;
USER is "ULFET"
SQL> update emp_clone set first_name='Ulfet' where
employee_id=101;
1 row updated.
SQL> delete from emp_clone where employee_id=102;
1 row deleted.
SQL> commit;
Commit complete.
As you see we changed 2 rows data (1 row updated, 1 row
deleted)
Create new comparison name to prevent conflict
SQL> set serveroutput on
DECLARE
scan_info
dbms_comparison.comparison_type;
scan_id INTEGER;
consistent BOOLEAN;
BEGIN
dbms_comparison.create_comparison(comparison_name
=>'comp_emp_new',
schema_name =>
'ulfet',
object_name => 'emp_clone',
dblink_name =>
null,
remote_schema_name => 'hr',
remote_object_name => 'employees'
);
consistent := dbms_comparison.compare(comparison_name =>
'comp_emp_new', scan_info => scan_info);
IF consistent = TRUE THEN
dbms_output.put_line('No
differences were found.');
ELSE
dbms_output.put_line('Differences were found.');
END IF;
dbms_comparison.converge(comparison_name =>
'comp_emp_new',
scan_id =>
scan_info.scan_id,
scan_info =>
scan_info,
converge_options =>
dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
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;
/SQL> 2 3
4 5 6 7 8
9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24
25 26 27 28 29 30 31 32
Differences were found.
Scan ID: 25
Local Rows Merged:2
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0
PL/SQL procedure successfully completed.
SQL> select employee_id, first_name from emp_clone where
employee_id in (101,102);
EMPLOYEE_ID FIRST_NAME
----------- --------------------
101 Neena
102 Lex
SQL>
That`s all.
For more detail please refer to: http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm#CJHCBCCI
