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

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

Answer 2

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 3

Here's a trigger I implemented to track DDL changes. Sources used:

http://www.dba-oracle.com/t_ddl_triggers.htm

http://www.orafaq.com/forum/t/68667/0/

CREATE OR REPLACE TRIGGER ddl_trig
AFTER create OR drop OR alter
  ON scott.SCHEMA
DECLARE
  li ora_name_list_t;
  ddl_text clob;
BEGIN
  for i in 1..ora_sql_txt(li) loop
     ddl_text := ddl_text || li(i);
  end loop;

INSERT INTO my_audit_tbl VALUES
    (SYSDATE,
     ORA_SYSEVENT,
     ORA_DICT_OBJ_TYPE,
     ORA_DICT_OBJ_NAME,
     ddl_text
    );
END;
/

Answer 4

Database Change Management / Database Diff Some tools for that are –

1) Oracle Change Management Pack

From the docs –

It allows us to take a baseline(snapshot) at a fixed time and then later we can see how the DB schema and objects have changed. The CMP can also generate DDL scripts, though I am not sure we would want to use it.

Details

• http://download-east.oracle.com/docs/cd/B19306_01/em.102/b31949/change_management.htm
• http://www.oracle.com/technology/products/oem/pdf/change-management-pack-11g-datasheet.pdf

2) PL/SQL Developer Compare User Objects feature

        This is available from Tools -> Compare User Objects

3) Oracle SQL Developer Database Diff feature

            This is available from Tools -> Database diff
            http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html#copy  See “Schema Copy and Compare”

#1 looks to be most versatile and flexible but DBA rights may be necessary.

#2 & 3 can be used by any developer. I think Oracle SQL Developer is easier and provides more options.

Using any of the above option can help in –

1. Identifying the changed objects and may also serve as a Check List before submission of MAC.
2. The developers concerned can take ownership of specific changed objects.

Answer 5

You can do this nicely with Toad.

You use the Compare Schemas function to find all the differences (it's very flexible; you can specify which object types to look at, and many other options). It will show you the differences, you can have a look and make sure it seems right, and then tell it to generate an update script for you. Voila. The only catch is, you need the DBA Module to generate the sync script, which is an extra cost. But I'd say it's worth it if you do this often. (Or if you can get hold of an older Toad version, pre-9.0 I think, there's a bug which allows you to extract the sync script without the DBA Module. :))

Toad isn't cheap, but having used it for years I consider it indispensable, and well worth the price for any Oracle developer or DBA.

Full disclosure, not that it's necessary: I am a former Quest employee and current shareholder.