Automated Oracle Schema Migration Tool

Question

What are some tools (commercial or OSS) that provide a GUI-based mechanism for creating schema upgrade scripts? To be clear, here are the tool responsibilities:

• Obtain connection to recent schema version (called "source").
• Obtain connection to previous schema version (called "target").
• Compare all schema objects between source and target.
• Create a script to make the target schema equivalent to the source schema ("upgrade script").
• Create a rollback script to revert the source schema, used if the upgrade script fails (at any point).
• Create individual files for schema objects.

The software must:

• Use ALTER TABLE instead of DROP and CREATE for renamed columns.
• Work with Oracle 10g or greater.
• Create scripts that can be batch executed (via command-line).
• Trivial installation process.
• (Bonus) Create scripts that can be executed with SQL*Plus.

Here are some examples (from StackOverflow, ServerFault, and Google searches):

• Change Manager
• Oracle SQL Developer

Software that does not meet the criteria, or cannot be evaluated, includes:

• TOAD
• PL/SQL Developer - Invalid SQL*Plus statements. Does not produce ALTER statements.
• SQL Fairy - No installer. Complex installation process. Poorly documented.
• DBDiff - Crippled data set evaluation, poor customer support.
• OrbitDB - Crippled data set evaluation.
• SchemaCrawler - No easily identifiable download version for Oracle databases.
• SQL Compare - SQL Server, not Oracle.
• LiquiBase - Requires changing the development process. No installer. Manually edit config files. Does not recognize its own baseUrl parameter.

The only acceptable crippling of the evaluation version is by time. Crippling by restricting the number of tables and views hides possible bugs that are only visible in the software during the attempt to migrate hundreds of tables and views.

Answer 1

Have you looked at the official tool for this?

Answer 2

I don't think SQLDeveloper is the way to go. It is great for migrating between different databases, not the incremental changes to a data model.

Answer 3

Schema Compare for Oracle should meet your requirements. This can be downloaded here:

http://www.red-gate.com/products/schema_compare_for_oracle/index.htm

Answer 4

Embarcadero Change Manager is the best tool for this onerous task.

Answer 5

Toad for Oracle with the DBA module does all of this quite nicely with the exception of the rollback script.

Answer 6

cx_OracleTools

If you don't mind command-line oriented tools this open source package will do what you require, with the exception of generating rollback scripts.

ExportObjects [options]

Export all of the objects in a schema as a set of directories named after the type of object containing a set of files named after the object itself.

GeneratePatch [options] FROMDIR TODIR

Generates a patch for differences in objects in two directories (which may have been created with ExportObjects) and ensures that the patch script can be executed without fear of encountering errors because of dependencies between objects.

http://cx-oracletools.sourceforge.net/