Automated Oracle Schema Migration Tool
- by Dave Jarvis
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.
