I make DDL changes using SQL Developer's GUI. Issue is, I have to apply individuals same changes towards the test atmosphere. I am wondering how others handle this problem. Presently I am needing to by hand write ALTER claims to create the exam atmosphere into alignment using the development atmosphere, but this really is vulnerable to error (doing exactly the same factor two times). In instances where there is no important data within the test atmosphere It's my job to just blow everything away, export the DDL scripts from dev and run them on your own in test.

I understand you will find triggers that may store each DDL change, but this can be a heavily shared atmosphere and I must avoid when possible.

Maybe I ought to just write the DDL stuff by hand instead of while using GUI?

I have seen a I-don't-know-how-many different ways attempted additional, as well as in finish I want to simply maintain manual scripts.

Now, you do not always need to write then yourself. In MSSQL, as you are creating a change, there's just a little button to create Script, that will goes a SQL script for that change you're making. I understand you are speaking about Oracle, and it is been a couple of years since i have labored using their GUI, however i are only able to imagine they have exactly the same feature.

However, you cannot escape from dealing with scripts by hand. You are going to possess a large amount of issues around pre-existing data, like default values for brand new posts or how to deal with data for any re-named/erased/moved column. Case area of the analysis when controling a database schema with time that you simply can't escape from. By trying to get this done by having an completely automated solution, your computer data will get screwed up eventually.

The main one factor I would suggest, simply to build your existence just a little simpler, is make certain you separate schema changes from code changes. The main difference is the fact that schema changes to tables and posts should be run exactly once rather than again, and for that reason need to be versioned as individual change scripts. However, code changes, like saved procs, functions, as well as sights, can (and really should) be run again and again, and may be versioned as with every other code file. A great way for this I have seen was whenever we had all the procs/functions/sights in VSS, and our build process would drop all and and recreate them throughout every update. This is actually the same idea as carrying out a rebuild of the C#/Java/whatever code, since it make certain things are always current.

Never make use of the GUI for may be. Write the scripts and set them into source control.

Here is a trigger I carried out to track DDL changes. Sources used:



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