Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tracking changes to the schema over time

RE: Tracking changes to the schema over time

From: Baumgartel, Paul <paul.baumgartel_at_credit-suisse.com>
Date: Thu, 3 May 2007 22:16:33 +0100
Message-ID: <D97D1FAE0521BD44820B920EDAB3BBAC1663C2A4@ENYC11P32005.corpny.csfb.com>


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:

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 Received on Thu May 03 2007 - 16:16:33 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US