Fog Creek Software
Discussion Board

Version control with Oracle?

I'm on an intranet project written in C# that uses Oracle 8i for a backend. We've got a test DB server and a production DB server.

Although all of our C# code is neatly tucked away in SourceSafe, we manage all of our stored procedures and database scripts with TOAD. I'd like to bring all of the database scripts and packages into SourceSafe and deploy them in the same manner in which we roll out our code.

Currently, if we ALTER a table on the test server, when it comes time to load production, we simply make the same change through TOAD on the production server. (Ack! Major possibility of screwing that up!) For stored procedures, we pretty much copy and paste the package from the test box to production.

I can't just re-run the DDL scripts since that will DROP the tables and all of the data. So how can I put my DDL under version control and deploy it in a safe manner?

For the stored procs, I guess I can just drop those into SourceSafe and then just re-create them on production each time we deploy. But is there a better way?

Any hints or suggestions on how to clean up the way that we handle database changes?

Mark Hoffman
Tuesday, July 1, 2003

I forget what it is called, but there is a product that you find that will do a diff between two databases.

Do the diff, and generate a script.

Make multiple databases on the test server that are copies of the current production data.  Run the diff_script and check for bugs. repeat, test, test, test.

Once you are confident, backup up production, run the script on production, push your app, execute your acceptance test, and get some Coors.


Tuesday, July 1, 2003

you can do a diff in tables with reports from microsoft access

Daniel Shchyokin
Tuesday, July 1, 2003

Oracle Has  a better tool for revision control.  Checkout their site.  I think it is something like Oracle Version Manager.

Adam Young
Tuesday, July 1, 2003

Does TOAD have an option to script changes? If not, then I'd have to recommend that you make all your changes via script. Run the script on the test server. If it does what you want, commit the script to VSS.

This is what you should be doing anyway, yes? Otherwise you're doing something different to production (running a script) than you did in dev (fiddling with a GUI)


Tuesday, July 1, 2003

TOAD (the professional version) has a "schema compare" option which works well. It compares two schemas and generates a diff script which can then be run on the production server.

If you're using the free version, a little more discipline might be required. Simply start from a point in time, commit the full structure SQL and stored procedures to CVS. Next time you make any sort of change, export the structures the same way and do a diff to find out the differences, then apply those changes to production.

Of course, if you're in the mind to write some (not a lot) PL/SQL and more importantly, if you're willing to upgrade to Oracle 9i, consider DBMS_REDEFINITION.. Does what you need, with the additional benefit of not needing to take the database down before you redefine tables. The problem with using diff scripts is that if someone is actually querying (inserting into) a particular table, you cannot run an ALTER table script on it. For a 24/7 operation, DBMS_REDEFINITION is essential.

deja vu
Tuesday, July 1, 2003

TOAD has a function that lets you check in/out stuff in SourceSafe, it can be turned on in the Options. Just point out the path to your VSS installation and TOAD reads it.

However, when I tried it the browser for the VSS projects were kindof sucky.

Friday, July 4, 2003

*  Recent Topics

*  Fog Creek Home