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.
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.
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=%3.* -synonyms=%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!