Ulfet`s Oracle Blog: Oracle 11g new features: DBMS

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

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

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

Ulfet`s Oracle Blog

Thursday, September 13, 2012

Oracle 11g new features: DBMS_COMPARISON

3 comments:

Cannot access dba_directories inside procedure

Total Pageviews

My Blog List