You could license the change management pack for grid control. It will give
you schema baselines and allow you to see diffs from those baselines. So
you could create a baseline called version1 when you were ready to upgrade,
you would run a comparison between current and baseline. If there are no
diffs, you are good to go otherwise, the changes will need to be reconciled
before the patch is applied. Once the patch is applied, create a new
baseline called version2. The rub with this solution is that it does const
extra $$ for the pack. Logging your patches to a database table is a good
solution also provided the environment is locked down and CM procedures are
followed. The optimal solution would be to use both of these techniques,
logging the patches to keep you in a known state and diffing the schemas to
check for changes made outside the process.
On 5/3/07, Eric Gross <egross_at_gridapp.com> wrote:
>
> 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
>
>
>
--
http://www.freelists.org/webpage/oracle-l
Received on Fri May 04 2007 - 11:34:31 CDT
