When we want to synchronize the data between same or multiple databases , oracle 11g provides new package called DBMS_COMPARISON. This is a procedural logic.
If this is a straightforward operation , please use MERGE statement.
Let us look at an example.
1. Let us create an user in the source database and create a table called emp from scott schema.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
In the destinaton database , let me create an user called testuser.
And also create a database link to the source database.
SQL> grant connect , dba to testuser identified by tesTusr ;
Grant succeeded.
SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
If this is a straightforward operation , please use MERGE statement.
Let us look at an example.
1. Let us create an user in the source database and create a table called emp from scott schema.
SQL> grant connect , dba to testuser identified by tesTusr ;
Grant succeeded.
SQL> create table emp as select * from scott.emp;
Table created.
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
In the destinaton database , let me create an user called testuser.
And also create a database link to the source database.
SQL> grant connect , dba to testuser identified by tesTusr ;
Grant succeeded.
C:\Users\mohideen>sqlplus testuser/tesTusr
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 22 17:14:31 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL> create database link
2 remotedb_link connect to srcuser
3 identified by srcusr
4 using 'remotedb';
Database link created.
SQL> select * from dual@remotedb_link;
D
-
X
SQL> create table emp as select * from srcuser.emp@remotedb_link where 1= 2;
Table created.
OK... We are all set . Let us sync it...
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6
7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
9 schema_name => 'testuser',
10 object_name => 'emp',
11 dblink_name => 'remotedb_link',
12 remote_schema_name => 'srcuser',
13 remote_object_name => 'emp');
14
15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
16 scan_info => scan_info);
17
18 IF consistent = TRUE THEN
19 dbms_output.put_line('No differences were found.');
20 ELSE
21 dbms_output.put_line('Differences were found.');
22 END IF;
23
24 dbms_comparison.converge(comparison_name => 'comp_emp',
25 scan_id => scan_info.scan_id,
26 scan_info => scan_info,
27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
28
29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
33 dbms_output.put_line('Remote Rows Deleted:' ||
34 scan_info.rmt_rows_deleted);
35 END;
36 /
DECLARE
*
ERROR at line 1:
ORA-23626: No eligible index on table TESTUSER.EMP
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 8
OOPS ... We need to add an index . Oracle compares the data by index . ie , The data between the source and the destination objects should be uniquely identifiable.
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6
7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
9 schema_name => 'testuser',
10 object_name => 'emp',
11 dblink_name => 'remotedb_link',
12 remote_schema_name => 'srcuser',
13 remote_object_name => 'emp');
14
15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
16 scan_info => scan_info);
17
18 IF consistent = TRUE THEN
19 dbms_output.put_line('No differences were found.');
20 ELSE
21 dbms_output.put_line('Differences were found.');
22 END IF;
23
24 dbms_comparison.converge(comparison_name => 'comp_emp',
25 scan_id => scan_info.scan_id,
26 scan_info => scan_info,
27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
28
29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
33 dbms_output.put_line('Remote Rows Deleted:' ||
34 scan_info.rmt_rows_deleted);
35 END;
36 /
Differences were found.
Scan ID: 1
Local Rows Merged:14
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0
PL/SQL procedure successfully completed.
Yes . We did it . The data has been synchronized. Oracle found there was a difference between the source and the destination objects . Since , we said synchorize the data if there were differences , it did it .
As you can see in the previous example ,
1. first , we have to create an comparison object ( line 8 )
2. second , we have to compare the objects ( line 15)
3. Based on the output of step 2, we sync ( converge) the data .. We can either say which site wins ( either REMOTE or LOCAL) .
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6
7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
9 schema_name => 'testuser',
10 object_name => 'emp',
11 dblink_name => 'remotedb_link',
12 remote_schema_name => 'srcuser',
13 remote_object_name => 'emp');
14
15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
16 scan_info => scan_info);
17
18 IF consistent = TRUE THEN
19 dbms_output.put_line('No differences were found.');
20 ELSE
21 dbms_output.put_line('Differences were found.');
22 END IF;
23
24 dbms_comparison.converge(comparison_name => 'comp_emp',
25 scan_id => scan_info.scan_id,
26 scan_info => scan_info,
27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
28
29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
33 dbms_output.put_line('Remote Rows Deleted:' ||
34 scan_info.rmt_rows_deleted);
35 END;
36 /
DECLARE
*
ERROR at line 1:
ORA-23627: Comparison object "TESTUSER"."COMP_EMP" already existed.
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 8
Errr.. Let us delete the comparison object and try again
SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6
7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
9 schema_name => 'testuser',
10 object_name => 'emp',
11 dblink_name => 'remotedb_link',
12 remote_schema_name => 'srcuser',
13 remote_object_name => 'emp');
14
15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
16 scan_info => scan_info);
17
18 IF consistent = TRUE THEN
19 dbms_output.put_line('No differences were found.');
20 ELSE
21 dbms_output.put_line('Differences were found.');
22 END IF;
23
24 dbms_comparison.converge(comparison_name => 'comp_emp',
25 scan_id => scan_info.scan_id,
26 scan_info => scan_info,
27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
28
29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
33 dbms_output.put_line('Remote Rows Deleted:' ||
34 scan_info.rmt_rows_deleted);
35 END;
36 /
No differences were found.
Scan ID: 4
Local Rows Merged:0
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0
PL/SQL procedure successfully completed.
SQL> delete from emp where empno = 7934;
1 row deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
PL/SQL procedure successfully completed.
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6
7 -- dbms_comparison.drop_comparison(comparison_name => 'comp_emp');
8 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
9 schema_name => 'testuser',
10 object_name => 'emp',
11 dblink_name => 'remotedb_link',
12 remote_schema_name => 'srcuser',
13 remote_object_name => 'emp');
14
15 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
16 scan_info => scan_info);
17
18 IF consistent = TRUE THEN
19 dbms_output.put_line('No differences were found.');
20 ELSE
21 dbms_output.put_line('Differences were found.');
22 END IF;
23
24 dbms_comparison.converge(comparison_name => 'comp_emp',
25 scan_id => scan_info.scan_id,
26 scan_info => scan_info,
27 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
28
29 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
30 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
31 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
32 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
33 dbms_output.put_line('Remote Rows Deleted:' ||
34 scan_info.rmt_rows_deleted);
35 END;
36 /
Differences were found.
Scan ID: 5
Local Rows Merged:1
Remote Rows Merged:0
Local Rows Deleted:0
Remote Rows Deleted:0
PL/SQL procedure successfully completed.
