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.