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!