Free and Easy Schema diff for Oracle

This post originated from a quick discussion we had internally on how to quickly and easily compare schemas between two Oracle databases. I learned about Sheeri Cabral’s post with a quick comparison solution for MySQL databases and I though of using a similar approach for Oracle. I did some testing and it worked quite well.

There certainly are tools in the market, free or not, that do this for us, and even generate scripts to correct differences. The steps below only go as far as to tell you what the differences are. However, they don’t require any additional tool and can be easily executed in any *nix or Windows environment.

The procedure uses the Oracle Data Pump utilities to extract one schema’s metadata and the Unix diff command to compare them. The metadata files generated by Data Pump, though, contain a lot of information about tables and indexes statistics that add too much noise to the diff output, since they usually differ a lot between databases. To eliminate that noise, I used the following small Perl script to pre-process the metadata files:

$ cat remove_stats_metadata.pl
my $print = 1;
while () {
  if (/^-- new object type path is: SCHEMA_EXPORT\/TABLE\/INDEX\/STATISTICS\/INDEX_STATISTICS/
      || /-- new object type path is: SCHEMA_EXPORT\/TABLE\/STATISTICS\/TABLE_STATISTICS/) { $print = 0; next };
  $print = 1 if (/^--/);

  print if ($print == 1 && ! /REUSE SETTINGS TIMESTAMP/);
}

The steps to compare one schema (HR, for example) between two databases are:

1. Export the metadata for the schema from the first database:

   $ expdp schemas=HR content=metadata_only directory=EXPORT_DIR dumpfile=hr_db1.dmp

2. Extract the metadata script from the dump file:

   $ impdp schemas=HR directory=EXPORT_DIR dumpfile=hr_db1.dmp sqlfile=hr_db1.sql

   Note: It’s important to ensure the SQLFILE parameter is specified to prevent impdp of importing the objects back into the database.

3. Do the same for the second database:

   $ expdp schemas=HR content=metadata_only directory=EXPORT_DIR dumpfile=hr_db2.dmp
   $ impdp schemas=HR directory=EXPORT_DIR dumpfile=hr_db2.dmp sqlfile=hr_db2.sql

4. Copy the *.sql files to the same location in one of the servers.
5. Process the two files with the Perl script to remove the “noise” from them:

   $ cat hr_db1.sql | perl remove_stats_metadata.pl > hr_db1_nostats.sql
   $ cat hr_db2.sql | perl remove_stats_metadata.pl > hr_db2_nostats.sql

6. Compare the two resulting files:

   $ diff hr_db1_nostats.sql hr_db2_nostats.sql
   4c4
   <  CREATE USER "HR" IDENTIFIED BY VALUES '19C18572A90CFCB1'
   ---
   >  CREATE USER "HR" IDENTIFIED BY VALUES 'D9926940AB71291D'
   52c52
   < sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DB1', inst_scn=>'5962427734');
   ---
   > sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'DB2', inst_scn=>'6196626062');
   766,772d765
   < CREATE TABLE "HR"."TEST"
   <    (  "A" NUMBER
   <    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
   <   STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
   <   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
   <   TABLESPACE "HR_DATA" ;

For Windows the same procedure can be used, replacing the diff command with any text file comparison tool available for Windows, like WinDiff.