Automated Oracle Schema Migration Tool

Posted by Dave Jarvis on Stack Overflow See other posts from Stack Overflow or by Dave Jarvis
Published on 2009-08-18T22:07:36Z Indexed on 2010/05/08 20:58 UTC
Read the original article Hit count: 328

Filed under:
|
|

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):

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.

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about schema