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.
Hello!
ReplyDeleteI have encountered the same problem during the creation of the comparison:
ERROR at line 1:
ORA-23626: No eligible index on table schema.table_name
ORA-06512: at "SYS.DBMS_COMPARISON", line 4793
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
ORA-06512: at line 8
I have altered the table by adding a pk constraint and still get this error.
Thank you!
Hi ,
ReplyDeleteIf you post a simple test case along with version information , I can take a look at it.
I have emp table as the source table and it's structure is:
Delete-ID raw(16) constraint pk_id primary key(ID)
-NAME varchar2(30)
The primary key requested is on the column ID which type is raw(16).
Could this be the problem that generates the error? i.e. pk on a raw type column is not supported in this case?
Thanks!
Please see below.
ReplyDeletehttp://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_comparison.htm
The DBMS_COMPARISON package cannot compare data in columns of the following datatypes:
•LONG
•LONG RAW
•ROWID
•UROWID
•CLOB
•NCLOB
•BLOB
•BFILE
•User-defined types (including object types, REFs, varrays, and nested tables)
Hi,
ReplyDeleteORA-23626: No eligible index on table schemaname.tablename
ORA-06512: at "SYS.DBMS_COMPARISON", line 5002
ORA-06512: at "SYS.DBMS_COMPARISON", line 448
While creating the table I had created it composite primary key (VARHCAR2 and NUMBER) but still I'm getting error. Can you please help me. Thank you.
Is it a partitioned table / index ? Then you may want to look at Metalink Doc ID ( ID 9302463.8)
DeleteI am not getting any issue . Please send me the test script .
See my example test case. It works fine.
Source
========
SQL> create table emptestsrc as select * from scott.emp;
Table created.
SQL> alter table emptestsrc add constraint emptestsrc_pk primary key(empno, ename);
Table altered.
Destination
==========
SQL> DECLARE
2 scan_info dbms_comparison.comparison_type;
3 scan_id INTEGER;
4 consistent BOOLEAN;
5 BEGIN
6 dbms_comparison.create_comparison(comparison_name => 'comp_emp',
7 schema_name => 'scott',
8 object_name => 'emptest',
9 dblink_name => 'qadb',
10 remote_schema_name => 'scott',
11 remote_object_name => 'emptestsrc');
12 consistent := dbms_comparison.compare(comparison_name => 'comp_emp',
13 scan_info => scan_info);
14 IF consistent = TRUE THEN
15 dbms_output.put_line('No differences were found.');
16 ELSE
17 dbms_output.put_line('Differences were found.');
18 END IF;
19 dbms_comparison.converge(comparison_name => 'comp_emp',
20 scan_id => scan_info.scan_id,
21 scan_info => scan_info,
22 converge_options => dbms_comparison.CMP_CONVERGE_REMOTE_WINS);
23 dbms_output.put_line('Scan ID: ' || scan_info.scan_id);
24 dbms_output.put_line('Local Rows Merged:' || scan_info.loc_rows_merged);
25 dbms_output.put_line('Remote Rows Merged:' || scan_info.rmt_rows_merged);
26 dbms_output.put_line('Local Rows Deleted:' || scan_info.loc_rows_deleted);
27 dbms_output.put_line('Remote Rows Deleted:' ||
28 scan_info.rmt_rows_deleted);
29 END;
30 /
PL/SQL procedure successfully completed.
SQL>