How should I migrate DDL changes from one environment to the next?

Question

I make DDL changes using SQL Developer's GUI. Problem is, I need to apply those same changes to the test environment. I'm wondering how others handle this issue. Currently I'm having to manually write ALTER statements to bring the test environment into alignment with the development environment, but this is prone to error (doing the same thing twice). In cases where there's no important data in the test environment I usually just blow everything away, export the DDL scripts from dev and run them from scratch in test.

I know there are triggers that can store each DDL change, but this is a heavily shared environment and I would like to avoid that if possible.

Maybe I should just write the DDL stuff manually rather than using the GUI?

Answer 1

I've seen a I-don't-know-how-many ways tried to handle this, and in end I think you need to just maintain manual scripts.

Now, you don't necessarily have to write then yourself. In MSSQL, as you're making a change, there is a little button to Generate Script, which will spit out a SQL script for the change you are making. I know you're talking about Oracle, and it's been a few years since I worked with their GUI, but I can only imagine that they have the same feature.

However, you can't get away from working with scripts manually. You're going to have a lot of issues around pre-existing data, like default values for new columns or how to handle data for a renamed/deleted/moved column. This is just part of the analysis in working with a database schema over time that you can't get away from. If you try to do this with an completely automated solution, your data is going to get messed up sooner or later.

The one thing I would recommend, just to make your life a little easier, is make sure you separate schema changes from code changes. The difference is that schema changes to tables and columns must be run exactly once and never again, and therefore have to be versioned as individual change scripts. However, code changes, like stored procs, functions, and even views, can (and should) be run over and over, and can be versioned just like any other code file. The best approach to this I've seen was when we had all of the procs/functions/views in VSS, and our build process would drop all and and recreate them during every update. This is the same idea as doing a rebuild of your C#/Java/whatever code, because it make sure everything is always up to date.

Answer 2

Never use the GUI for such things. Write the scripts and put them into source control.