DBMS_COMPARISON supplied package after 11g Release 1

I discussed 10g’s ORA_HASH SQL function to determine if two Oracle tables’ data are equal or not with this post before, especially if you are working with huge tables and they are on different locations this method is very effective.

In the same post I also mentioned some favorite schema and data comparison methods used within the community, so this one is a following post to that post to mention the new 11g supplied package; DBMS_COMPARISON.

After a fast startup demonstration based on psoug’s library I saw that this new feature also uses ORA_HASH SQL function. :) Also for the schema comparison part which dictionary checks are made can be followed inside a 10046 SQL trace as demonstrated below;

conn hr/hr

DROP TABLE depts PURGE ;

CREATE TABLE depts NOLOGGING AS SELECT * FROM departments ;

CREATE UNIQUE INDEX ui_depts ON depts(department_id) NOLOGGING ;

DELETE FROM depts WHERE department_id = 10;

1 row deleted.

COMMIT;

ALTER SESSION SET max_dump_file_size=unlimited;
ALTER SESSION SET timed_statistics = true;
ALTER SESSION SET statistics_level = ALL ;

ALTER SESSION SET tracefile_identifier = dbms_comparison ;

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 8';

exec dbms_comparison.create_comparison(comparison_name=>'compare_test', schema_name=>'HR', object_name=>'DEPTS', dblink_name=>NULL,  remote_schema_name=>'HR', remote_object_name=>'DEPARTMENTS',  scan_percent=>90);

set serveroutput on

DECLARE
  retval dbms_comparison.comparison_type;
BEGIN
  IF dbms_comparison.compare('compare_test', retval, perform_row_dif=>TRUE) THEN
    dbms_output.put_line('No Differences');
  ELSE
    dbms_output.put_line('Differences Found');
  END IF;
END;
/
Differences Found

PL/SQL procedure successfully completed.

ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

select value from v$diag_info where name = 'Default Trace File' ;

VALUE
----------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc

CMD > tkprof /u01/app/oracle/diag/rdbms/tong/tong/trace/tong_ora_4402_DBMS_COMPARISON.trc tong_ora_4402_DBMS_COMPARISON.txt sys=yes waits=yes sort=execpu

set null @
set colsep |
set linesize 2500

select *  FROM  user_comparison_row_dif ;

COMPARISON_NAME               |   SCAN_ID|LOCAL_ROWID       |REMOTE_ROWID

INDEX_VALUE

STA|LAST_UPDATE_TIME

COMPARE_TEST                  |         4|@                 |AAARADAAFAAAAA3AAA
10
DIF|04-OCT-08 02.58.03.306182 PM

exec dbms_comparison.drop_comparison(comparison_name=>'compare_test') ;

There are several constraints to use this method like the local database that runs the subprograms in the DBMS_COMPARISON package must be at least 11.1 and the remote database must be 10.1 at least. Non-Oracle databases are not supported and the database character sets must be the same for the databases being compared. But I am pretty sure with feature releases this package will be enhanced and be used with other database features.

ps: During my unconference session at OOW08 – APEX Test Drive – I mentioned that Apex 3 on 11.1 under Utilities tab also has a GUI support to use this new supplied package.