Thursday, September 13, 2012

Oracle 11g new features: DBMS_COMPARISON


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.





Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...