How to Compare Two Database Objects in Oracle?

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;
/

Output:

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;
/

Output:

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';

Output:

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;

See also:

• Split a String in PL/SQL Example
• Extract DDL Statements from the Oracle DUMP (dmp file)