RE: Tracking changes to the schema over time
- From: "Baumgartel, Paul" <paul.baumgartel@xxxxxxxxxxxxxxxxx>
- To: "'egross@xxxxxxxxxxx'" <egross@xxxxxxxxxxx>, "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 3 May 2007 22:16:33 +0100
Eric,
We did this at my previous job. Versions were logged in a table, and of course
the row for the new version wasn't inserted until the patch script completed
successfully. It's true that this doesn't guarantee the schema to be at the
stored version, but it's a start. I suppose you could also write a program
that would create a checksum of sorts by applying a function to the definitions
of all (or selected) database objects so that you could do a quick comparison
to the expected value, but that might be overkill.
The other thing to keep in mind is that the upgrade scripts should be
re-runnable in case of a failure.
Paul Baumgartel
CREDIT SUISSE
Information Technology
Securities Processing Databases Americas
One Madison Avenue
New York, NY 10010
USA
Phone 212.538.1143
paul.baumgartel@xxxxxxxxxxxxxxxxx
www.credit-suisse.com
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Eric Gross
Sent: Thursday, May 03, 2007 3:48 PM
To: Oracle-L @ freelists.org
Subject: Tracking changes to the schema over time
Hello everyone-
I am looking to solve a problem that I hope already has been solved by
some of you. We have a database schema that changes over time in subtle
ways (a column's datatype changes or a stored procedure changes). The
version of the schema in use is tightly coupled with the version of the
application in use so as the application is upgraded, so too must the
database schema. Each version will have a patch SQL script that will
update from the previous version to the current version so I just need
to know the current version and then run all the patches between that
version and the current version.
How can I determine what version of a schema is in use so that I can run
the required scripts to upgrade it to the current version?
Options I have considered:
* A table with one row per version where you would insert a new
row each upgrade. The issue with this method is that there is no
guarantee that the version indicated in this table matches up with the
actual schema (perhaps an upgrade broke and didn't do everything it was
supposed to do).
* I could write a custom script to inspect every aspect of every
object to confirm its version. This sounds really hard and I can't
think of any way for DBMS_METADATA to help me out.
This is 10gR2. The objects in question are tables, mviews (and their
snapshots & refresh groups), indexes, functions, and procedures.
Thanks,
e.
Eric Gross
GridApp Systems
Mr. Database
egross@xxxxxxxxxxx
p: (646) 452-4050
--
//www.freelists.org/webpage/oracle-l
==============================================================================
Please access the attached hyperlink for an important electronic communications
disclaimer:
http://www.credit-suisse.com/legal/en/disclaimer_email_ib.html
==============================================================================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
