DBMS_COMPARISON is a new package introduced by oracle in database 11g which is used for comparing database objects in different databases.
The DBMS_COMPARISON package can compare the following types of database objects:
b- Single-table views
c- Materialized views
d- Synonyms for tables, single-table views, and materialized views
The DBMS_COMPARISON package cannot compare data in columns of the following data types:
b- LONG RAW
i- User-defined types (including object types, REFs, varrays, and nested tables)
j- Oracle-supplied types (including any types, XML types, spatial types, and media types)
These is the steps to compare a database object that is shared at two different databases
1-Run the CREATE_COMPARE procedure in this package to create a comparison.
2-Run the COMPARE function in this package to compare the database object at the two databases and identify differences. This function returns TRUE when no differences are found and FALSE when differences are found. This function also populates data dictionary views with comparison results.
Separate comparison results are generated for each execution of the COMPARE function.
Note that you can run COMPARE function at anytime after running CREATE_COMPARE procedure, each time you run COMPARE function it records comparison results in appropriate data dictionary views.
3-Use the below Data Dictionary views to examine the comparison results
4- If there are differences, and you want to synchronize the database objects at the two databases, then run the CONVERGE procedure in this package.
5- DBMS_COMPARISON supplied another subprograms
DROP_COMPARISON Procedure : Drops a comparison
PURGE_COMPARISON Procedure : Purges the comparison results, or a subset of the comparison results, for a comparison
RECHECK Function : Rechecks the differences in a specified scan for a comparison
DemoLet's now create a demo regarding to the previous steps.
I will compare two database objects in different schema at the same database, If they are in different databases then you should create database link.
Suppose I have two schema ( MCIT_CMS , MCIT_CMS_2) which they have EMPLOYEES_VIEW table.
Note the table must have the same structure otherwise you will get the following exception
ORA-23625: Table shapes of MCIT_CMS.EMPLOYEES_VIEW and MCIT_CMS_2.EMPLOYEES_VIEW@ did not match.
1- Create Comparison
BEGIN DBMS_COMPARISON. CREATE_COMPARISON (comparison_name => 'demo_comparison', schema_name => 'MCIT_CMS', object_name => 'EMPLOYEES_VIEW', dblink_name => NULL, remote_schema_name => 'MCIT_CMS_2', remote_object_name => 'EMPLOYEES_VIEW'); END;
Note I passed dblink_name parameter as null value because two schema are in the same database.
2- Run COMPARE function
DECLARE has_no_difference BOOLEAN; scan_info DBMS_COMPARISON.COMPARISON_TYPE; BEGIN has_no_difference := DBMS_COMPARISON. COMPARE (comparison_name => 'demo_comparison', scan_info => scan_info, perform_row_dif => TRUE); DBMS_OUTPUT.PUT_LINE ('Scan ID: ' || scan_info.scan_id); IF has_no_difference = TRUE THEN DBMS_OUTPUT.PUT_LINE ('No differences were found.'); ELSE DBMS_OUTPUT.PUT_LINE ('Differences were found.'); END IF; END;
It prints "Differences were found." in DBMS console if changes were existed. and it take unique Scan Id in my database it is 3, it may take different number at your database.
3- Query comparison data dictionary views.
SELECT * FROM USER_COMPARISON_SCAN; SELECT * FROM USER_COMPARISON_SCAN_VALUES; SELECT * FROM USER_COMPARISON_ROW_DIF;
4- Synchronize the differences between two objects
You should change SCAN_ID number with your generated SCAN_ID from previous step in your database(Number in red color).
DECLARE SCAN_INFO DBMS_COMPARISON.COMPARISON_TYPE; BEGIN DBMS_COMPARISON. CONVERGE (COMPARISON_NAME => 'demo_comparison', SCAN_ID => 3, SCAN_INFO => SCAN_INFO, CONVERGE_OPTIONS => DBMS_COMPARISON.CMP_CONVERGE_LOCAL_WINS); 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;
5- Drop Comparison
BEGIN DBMS_COMPARISON.DROP_COMPARISON ('demo_comparison'); END;
6- Purge Comparison
BEGIN DBMS_COMPARISON.PURGE_COMPARISON ('demo_comparison'); END;