Oracle databases are complex enough without active development, testing, and patching taking place. In most environments where custom applications are being developed or out-of-the-box database code is being extended, the database code and data between database environments inevitably deviates or is corrupted over time.
Normally, whenever an environment is in an unknown state, I say, “Refresh the environment from production.” Sometimes that reply is not acceptable to the development or project team because code may not have been checked into source control or the refresh might wipe out too many other things, pushing back the project timeline. More often than not, it’s because the developers do not know what they’ve done or how to get the system back to where they want it. 🙂
Challenges such as these led me down the path of investigating schema comparison tools. Several vendors and open source projects provide such tools, and I tried out a few of them using the following criteria:
- Reasonable price (less than $400 USD).
- Easy to use.
- Compare as many database schema object types as possible.
- Perform accurate comparisons.
- Perform quick comparisons.
- Command line interface in case automation requirements ever arise.
After defining the requirements, I searched online for available tools to test. If a demo or trial product was unavailable online or was more than $1000.00, I did not test it or include it in the list. From there, I products dropped out of the list as they failed to meet the inclusion criteria. In the end, the Red Gate Schema Compare for Oracle and dbForge Schema Compare for Oracle ended up being comparable. The accuracy and speed of the two finalists was measured by doing a comparison of the thousands of PL/SQL objects in an Oracle E-Business Suite database. The comparison took quite a while, but both products were able to do it. There were some discrepancies between the differences reported by each tool, but they were not material.
|Product Name||Price (USD)||Ease of Use||Object Types||Accurate||Fast||Command Line|
|Datanamic SchemaDiff for Oracle||$299.00||Fail||–||–||–||–|
|dbForge Schema Compare for Oracle||$229.95||Pass||Pass||Pass||Pass||Pass|
|DbSchema Database ER Diagram Design Tool||$127.00||Fail||–||–||–||–|
|DBSolo Schema Comparison Tool||$129.00||Fail||–||–||–||–|
|EMS DB Comparer for Oracle||$133.00||Fail||–||–||–||–|
|Red Gate Schema Compare for Oracle||$495.00||Pass||Pass||Pass||Pass||Pass|
After coming down to the schema compare finalists, I also tested the data compare tools from Red Gate and Devart. Both data comparison tools were comparable in my testing.
In the end, the choice came down to pricing between Red Gate and Devart. I contacted Red Gate to see if they could be competitive with the Devart dbForge pricing, but they were not able to match the price. I am guessing that Red Gate has a bigger marketing department and more overhead due to being in the UK while Devart does not seem to have as big of a web presence and is in the Ukraine.
Both tools get the job done, but I opted for the dbForge Compare Bundle for Oracle costing $289.95 USD.
If you have a different opinion about any of the products, please let me know. I rapidly tested and dropped several of the products off of the list, because they were not intuitive to me or required too much setup for a quick test, but it could easily have been my own impatience or ignorance. 🙂