oraDifference.py

oraDifference.py - a tool for comparing items that differ between two Oracle schemas. The basic idea is to leverage the excellent graphical diff/merge tools available for file comparison and conveniently use them to inspect database object differences.

There are many programs that can compare two database schemas and tell you which objects are defined differently between them. That's really not good enough, though, because you then need to tediously dig into the definition of each (allegedly) differing object by hand, and perform any desired reconciliation by hand.

I wrote oraDifference.py to make comparing and reconciling schemas more convenient. For example, let's say you have the SCOTT schema in production and development instances. Stored function MYFUNC is defined in both, but the definition differs. View MYVIEW is defined only in development. Then running
python oraDifference.py scott@prod scott@dev
will generate the following batch files (Win) or shell scripts (*nix):

• oraDifferenceResults/FUNCTION/MYFUNC.bat, which will invoke a graphical diff/merge tool showing you precisely where MYFUNC's definition differs between the two instances
• oraDifferenceResults/FUNCTION/MYFUNC-copy-SCOTT-DEV.bat, which will write DEV's definition of FUNC into PROD
• oraDifferenceResults/VIEW/missingFrom-SCOTT-PROD/MYVIEW.sql, the definition of MYVIEW
• oraDifferenceResults/VIEW/missingFrom-SCOTT-PROD/MYVIEW-copy-SCOTT-DEV.bat, which writes MYVIEW into PROD

For now, you have to do the work of getting oraDifference.py (and sqlWrap.py) manually and putting them someplace appropriate. I do intend to wrap them up in a proper distutils distribution (maybe even with an .egg).

I'm posting this now because I find it really useful already. You may find some of my design decisions quirky - for instance, I mush all one-liner items (grants, synonyms) into big files by category, rather than making separate files for each grant or synonym. It's Python, though, so you should be able to tweak it to meet your tastes. Also, you can tweak the process oraDifference.py uses to decide whether two objects differ. I have always been annoyed that I can't stop TOAD's "Schema Compare" tool from turning up dozens of "differences" that I consider false hits. With oraDifference.py, you can just get in there and change it.

Eventually I hope to release something that will look polished and final, but for now, feel free to use it, and re-code any part that doesn't match your preferences - and let me know about any of your changes that you think should go into everybody's version.

For fairness, I'll mention some other options I found for schema comparison...

• LivingLogic's oradiff.py (part of ll.orasql) is the closest to oraDifference.py. It also compares the text for each object, but it outputs in unified diff format (or unidiff). If you can read unidiff comfortably - welcome, advanced extraterrestrial visitor! The oradiff.py source code looks tidy and well-organized, but it's still not obvious to me how to tweak it.
• schemaCompare, a Java program, was registered at SourceForge in June 2002, but has not yet released any files. I conceived oraDifference.py about two weeks ago. Not to suggest that this implies anything about the relative productivity of various languages. (jab, jab)