In this tutorial, I am giving an example to compare two database table objects of different schemas using the DBMS_COMPARISON utility package in Oracle.
Steps to Compare Two Table Objects in Oracle Using DBMS_COMPARISON
Step-1 Create the comparison using DBMS_COMPARISON. In the following example, it will compare the one table from the SCOTT schema and one table from the HR schema in the same database and will create a comparison named emp_compare.
BEGIN DBMS_COMPARISON.create_comparison ( comparison_name => 'emp_compare', schema_name => 'scott', object_name => 'emp', dblink_name => NULL, remote_schema_name => 'hr', remote_object_name => 'emp2'); END; /
PL/SQL procedure successfully completed.
Step-2 After executing the above PL/SQL block, the comparison would be created. The next step is to run this comparison as shown below.
SET SERVEROUTPUT ON DECLARE t_scan_info DBMS_COMPARISON.comparison_type; l_diff BOOLEAN; BEGIN l_diff := DBMS_COMPARISON.compare ( comparison_name => 'emp_compare', scan_info => t_scan_info, perform_row_dif => TRUE ); IF NOT l_diff THEN DBMS_OUTPUT.put_line('Differences found and scan_id is ' || t_scan_info.scan_id); ELSE DBMS_OUTPUT.put_line('No differences found.'); END IF; END; /
Differences found and scan_id is 7 PL/SQL procedure successfully completed.
Step-3 If differences found then you can check the differences by the following query:
SELECT comparison_name, local_rowid, remote_rowid, status FROM user_comparison_row_dif WHERE comparison_name = 'EMP_COMPARE';
COMPARISON_NAME LOCAL_ROWID REMOTE_ROWID STATUS EMP_COMPARE AAAR3sAAEAAAACXAAA AAAU5vAAEAAAAW9AAA DIF EMP_COMPARE AAAR3sAAEAAAACXAAD AAAU5vAAEAAAAW9AAD DIF
You will get the output as shown above. In which it will show you the comparison name, local row id (scott.emp table row id), remote row id (hr.emp2 table row id) and the status.
Now you can query both the tables for these ROWIDs to check the differences.
You can also compare the two tables data from the different schema using the SQL query, as shown in below example.
SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM scott.emp MINUS SELECT EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO FROM hr.emp2;