Comparing Oracle DB objects from different schemas

Today, we have been working on a migration from webMethods 7.1.2 to 8.0 at a customer of ours. Those who are familiar with webMethods probably know that the objects in a database for one of the webMethods components (Integration Server, Trading Networks, My webMethods Server) may slightly start to differ between environments after some time, because of the installation, testing or roll back of various patches, service packs, etc...

This may lead to errors or differences in the behaviour of the webMethods DB configurator when migrating your development, test, production or other environments. Then it can be helpful to be able to quickly compare all the objects in your DB schemas, to make sure all your environments are in sync. There is no out-of-the-box way of comparing all objects in different Oracle databases or schemas. To solve this challenge, you can extract the DDL of the Oracle database objects creating an SQL script as follows:


1) First you need to determine all the objects in the Oracle schema you want to analyse and extract the DDL for it. This can be achieved by creating an "extractDDL.sql" SQL script, based on the query below (you can't extract the metadata for a LOB object). You should execute this query for each environment using Oracle's SQL Developer or some other SQL IDE with the Oracle user that owns the database schema:

select 'select dbms_metadata.get_ddl('''||OBJECT_TYPE||''','''||OBJECT_NAME||''') from dual;'
from user_objects
where object_type != 'LOB'
order by OBJECT_NAME asc


2) The list of statements returned by the above query can be copied into an SQL script, which you can execute using sqlplus and that will result into a list of DDL statements you can easily compare. The scripts looks something like this:

set heading off;
set echo off;
set pages 999;
set long 90000;
set trims on;
spool ddl_list_enviromentX.sql

select dbms_metadata.get_ddl('TABLE','ACTIVITYLOG') from dual;
select dbms_metadata.get_ddl('TABLE','ADDRESS') from dual;
select dbms_metadata.get_ddl('TABLE','ARCHIVESERVICE') from
...
select dbms_metadata.get_ddl('TABLE','TPA') from dual;
select dbms_metadata.get_ddl('TABLE','TPALOCK') from dual;

spool off;

The statements in italic are copied from the result of the SQL statement in step 1. Spool the results to a different file for each environment. Executing the script using sqlplus can be done as follows from the command line:

sqlplus <username>/<password>@<db tns name> @extractDDL.sql


3) After you have executed the script from step 2 in each environment you want to compare the Oracle DB objects for, you can use a text comparison tool such as WinMerge to quickly determine the differences between 2 or more result files. You may need to perform some small manipulations on the files produced in step 2 to come to perfectly comparable results, because the dbms_metadata function may return indexes on tables for example in a different order per environment, but all in all, this technique should allow you to fairly quickly determine the potential differences between 2 Oracle database schemas that should be identical.

Author: Kristof Lievens