In an 11gr1 installation, Oracle’s SQL Developer tool tempts you with a Schema Diff Tool. However, it’s totally broken in every R1 installation I’ve tried. It just doesn’t run. It is however fixed in R2. My next issue is that I can’t actually get the 11gr2 installation to work at all on my development machine. So I tried to find an alternative.
I also wanted to see more detail on the objects in the difference quickly, something I couldn’t get the SQL Developer environment to do. In addition to that, the UI takes an age to start.
I came across SchemaCrawler , a LGPL Open Source tool that can be integrated into some automated tests.
- First install JDK 7 – http://www.oracle.com/technetwork/java/javase/downloads/index.html. I first tried to dump the SchemaCrawler files in the oracle JDK/JRE directories but that didn’t go to well!
- Then copy the sc.cmd and lib directory into the C:\Program Files (x86)\Java\JDK1.7.0_45\ folder (version may be different)
- You’ll need to download the Oracle JDBC driver and place it in the lib directory, available here. Yes, you’ll need an Oracle Technology Network account (sigh). I used the odbc5.jar download.
- If java isn’t in your path, add it or modify sc.cmd to say bin/java.
It took me a reasonable amount of time to work out the numerous command line options to SchemaCrawler, and found very few examples. So I’ve created a batch file to help simplify usage for others.
Here’s the contents of Crawl.bat
rem crawl.bat host databaseSID schemaname password >>outputfile
sc -host=%1 -database=%2 -user=%3 -password=%4 -c=details -infolevel=maximum -schemas=(?i).*%3.* -synonyms=(?i).*%3.* -outputformat=text
And an example usage
crawl 192.168.100.2 ORCL MYORASCHEMA P4ssw0rd >dump.txt
You can then use tools like WinDiff or WinMerge to quickly see the fundamental meta-changes made between phases of schema development.
It’s work in progress, and unfortunately includes some system generated constraint and check identifiers like SYS_C0014903 which make it more difficult to WinDiff. Feel free to suggest the regex format specifier I need, as I hate the stuff!
Access violation 
I have managed to use Tools->Filters->LineFilter in WinMerge, and added ^(SYS). to eliminate system generated constraints.
Unfortunately the output doesn’t contain the tablespace configuration of tables or their growth rates (yet).
Giles,
You can use a properties file to further control SchemaCrawler output. For example, you can suppress system generated constraint and check identifiers. You can also add in details about tablespace configuration by running custom SQL, Please email me directly if you need help.
Sualeh.
Hey Sualeh,
Thanks for the reply.
I didn’t know about the properties file! Thanks for the tip, I’ll have a look soon.
Ideally I would like to
* suppress the system generated constraints and checks
* select the configuration of the dba_tablespaces that hold objects from a given schema
* output the definition of any stored procedures in the schema
* output the growth configuration of tables
I’ll let you know if I get stuck. Thanks for the offer.
Updated batch file to include case insensitive wildcard search. Also added some install instructions as I foolishly tried to install into the oracle’s JDK directory, and the JDK 7 download doesn’t set the PATH, so had to modify sc.cmd.