Re: Tracking changes to the schema over time

Another approach is that each version is built in a schema that has the version in the schema name. The application runs off of synonyms. New version is put into new schema. Switch to new version by re-pointing the synonyms. This approach makes testing the patch before putting it into production straightforward and makes the production move simple. Downside is that you will need some additional disk space...

On 5/3/07, Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com> wrote:
>
> 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_at_credit-suisse.com
> www.credit-suisse.com
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> 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_at_gridapp.com
> p: (646) 452-4050
> --
> http://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
>
> ==============================================================================
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Rumpi Gravenstein

--
http://www.freelists.org/webpage/oracle-l