Re: Dependencies and invalidations.

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Sun, 23 Apr 2006 01:15:54 GMT
Message-Id: <pan.2006.04.23.01.15.54.396160@sbcglobal.net>

On Sun, 23 Apr 2006 02:20:10 +0200, Maxim Demenko wrote:

> Mladen Gogala schrieb:

>> In Oracle RDBMS 10.2 it is practically impossible to invalidate a PL/SQL
>> object. I tried with adding indexes, dropping primary key 

>
> Not every ddl invalidate the dependent procedures, only changing layout
> of tables ( i.e. add/drop/modify columns, maybe something else, but not
> add/drop constraint, alter table move etc.)

Those DDL can change plans of the underlying SQL statements and, therefore, should invalidate the dependent procedures.

>
> and analyzing

>> the underlying tables, 

>
> Here you are joking - right ?

Nope. No jokes here. Analyzing the underlying tables can also drastically change plans and should, therefore, invalidate the procedures.

>
> but the PL/SQL procedure build on top of the table

>> remained valid. Oracle 9i PL/SQL objects were much more sensitive. 

>
> You probably don't have 9i instance right now to test, the behaviour is
> the same in 9i and even 8i.

You are right, I don't have access right now. I will check it on Monday, though.

-- 
http://www.mgogala.com

Received on Sat Apr 22 2006 - 20:15:54 CDT