Re: change schema of installed db

Parris Geiser (parris_at_walleye.esp.bellcore.com) wrote:

> Hi,


> 	We have a database installed at a customer site. In the


> 	next release of our sw we have to make the following types

> 	of changes to our schema:

> 	1. Add new columns to a table (both NULL and NOT NULL).

           Adding NULL columns; no down time necessary
             use alter table ADD column
           Adding NOT NULL columns; no down time necessary
             use alter table ADD column -- adds a nullable column
             update the value of the field to a not null value
             use alter table to make the column not nullable



> 	2. Delete existing columns.


           Table would become unavailable for a short while
           sqlplus
              rename {table} to old_{table}
              alter table old_{table} and drop any constraints
              create the table {table} with the column removed
              using a sqlplus script copy the necessary column info
              from old_{table} to {table}
              drop old_{table}
              make any indexes etc. on {table}



> 	3. Change the names of existing columns (some that appear in a key).


           I don't understand fully what do you mean; if your intent
           is to change the columns in a key:
           use alter table to drop the key
           use alter table to create the key.
           -- a lock is placed while an alter table is executing
           -- better if you let the client know that some interruption
           -- may occur
           -- also if a query is started with no index, that may take 
           -- a long time.


> 	4. Increasing/Decreasing the size of columns.


           Alter table -- no down time necessary



> 	5. Adding a new foreign key to a table.


           Alter table 

hope it helps

--
**************************************************************
*                          Saad Ahmad                        *
*                          McHugh Freeman & Associates, Inc. *
*                          Ph:  (414) 798-8606 Ext. 457      *
*                          Fax: (414) 798-8619               *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************