Re: advice on approach for db upgrade script

From: DA Morgan <damorgan_at_psoug.org>
Date: Wed, 16 May 2007 13:15:12 -0700
Message-ID: <1179346509.366961@bubbleator.drizzle.com>

Jeff Kish wrote:

> On 16 May 2007 09:37:11 -0700, fitzjarrell_at_cox.net wrote:
> 

>> On May 16, 10:57 am, Jeff Kish <jeff.k..._at_mro.com> wrote:
>>> Greetings.
>>>
>>> I need to write a script or program (I prefer a pl/sql script) to upgrade a
>>> database.
>>>
>>> The task needs to:
>>> ensure the user has minimum length required for various table.columns
>>> save off old rows in some saved tables
>>> create new data rows (the data making up the primary keys are changing)
>>> update all the referring columns to use the rows with the new primary key
>>> values
>>> delete the old data rows that have the old primary key values
>>> delete the saved tables
>>>
>>> warn the user if something is wrong and if so:
>>> - clear out data and delete any temp objects
>>> - rollback any transactions
>>> - exit
>>>
>>> i.e. leave things either as they were or as they should be after the upgrade
>>>
>>> I'm not sure the best approach.
>>> I've read about pl/sql tables, temporary tables, and global temporary tables.
>>> I'm not sure the best approach here. I'd appreciate any insight or
>>> recommendations. The oracle database will be 9.2 or later.
>>>
>>> thanks
>>> Jeff Kish
>> Please define what you mean by 'upgrade a database'.
>>
>>
>> David Fitzjarrell

> I have an application suite that runs on off of data found in a schema in a
> database.
> I need to upgrade the schema by applying ddl and dml so it runs with a newer
> version of code.
> 
> I think it is ok in my situation to ask the user to ensure that certain
> columns are made a certain size before running the 'upgrade' script, so I
> guess I need to assemble an upgrade script to upgrade the customer data in the
> schema.
> 
> So by 'upgrade a database' is not an 'oracle upgrade', but upgrading some
> column sizes, and altering the data values in some primary keys in some tables
> in the customers schema.
> 
> thanks
> Jeff Kish

Look at the DBMS_REDEFINITION built-in package.

Working demo in Morgan's Library at www.psoug.org.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Wed May 16 2007 - 15:15:12 CDT