CODEWITHSUNDEEP
oracle-databaseversion-controloracle11gschemaddl
Alexx
Alexx

Reputation: 3692

How to generate DDL for deltas only between two different versions of a schema?

I have read both TOAD and Liquibase can do this but I don't have licenses. We have access to OEM and are using JDeveloper/SQLDeveloper but neither seem to have a tool to generate the DDL for deltas only between two schemas.

This is to solve schema versioning issues, where we have vx.x of a schema in the test environment and vx.y in development, and desire to generate DDL deltas between (x.x-x.y) in order to generate and check in deployment scripts.

Currently I am managing all of this manually by getting scripts from developers and building one single update script by hand.

Upvotes: 2

Views: 2128

Answers (2)

Alexx
Alexx

Reputation: 3692

I did find the awesome diff utility in SQLDeveloper, which is hugely helpful (and not so much in OEM?).

I was ultimately looking for a handy way to generate the delta DDL. For example, SCHEMA_2.TABLE_A is (foo number(22), bar varchar2(100)), and SCHEMA_1.TABLE_A is (foo number(22), bar varchar(100), baz CLOB). Looking for a utility to generate

ALTER TABLE SCHEMA_1.TABLE_A add column (baz CLOB)

I'm guessing it's a pipe dream to get that 'for free' in the tools I have. :)

We're using Flyway for our developers, and I guess I'm going to just have to manually manage the migrations for now.

Thanks to everyone for the great suggestions!

Cheers, Alexx

Upvotes: 0

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6346

Try to use package dbms_metadata_diff. Example 

   select DBMS_METADATA_DIFF.COMPARE_ALTER('TABLE','COUNTRIES','COUNTRIES2') from dual

Upvotes: 3

Related Questions