Re: Using Packages makes ALTER TABLE dangerous

Joachim Zobel <jzobel_at_my-dejanews.com> wrote:

>
>Hi.
>
>Today the following happened:
>When recreating several tables on friday afternoon I had forgotten a
>DEFAULT. I noticed this while checking the error logs this morning and
>then did the ALTER TABLE to set the DEFAULT. This was a mistake.
>
>All write access to this table goes through a package. When I ALTERed the
>table, this package got invalid. Unfortunately there where active sessions
>using the package, which in fact where idly hanging around due to bad
>application error handling. So the package could not be compiled and all
>write access to the table was blocked.
>
>Several questions arise:
>Will this behaviour improve with oracle 9 (we are using 8.1.7)? Is there a
>way to use packages for table encapsulation and still be able to do ALTER
>TABLEs without fear?
>
>Thanx,
>Joachim

( unrequested advice follows, feel free to ignore)

I would never allow alter table statements to be executed against a running, production database instance ( we don't allow changes to a production instance in any event, just to a development and testing one and then, if it works , we migrate it to production on a pre-announced time) ..
If you need to anyway:

1: notify the users
2: shut down the instance
3: restart it in restricted mode 
4: do the changes 
5: check for consequences ( especially unintended ones)
6: restart normally and notify the users.

Just my 2c... Received on Mon Dec 08 2003 - 12:50:30 CST