Re: Shared Pool causing packages to automatically go invalid!

Paula,

Just an idea... package status is stored somewhere in the dictionary so invalidation should cause a datablock change in the system tablespace and obviously generate some redo. Now from there on - Log Miner is your friend (if my memory serves me ok, it comes with 8i and can analyze redos from 8.0 even). If you are really desperate - redo block dumps but that's more tricky.
Of some relevance might be this note I posted recently - http://www.pythian.com/blogs/269/oracle-logminer-helps-investigate-security-issues

Cheers,
Alex

On 11/1/06, Paula Stankus <paulastankus_at_yahoo.com> wrote:
> Guys,
>
> Version: Oracle 8.1.7 - sigh
>
> I have been trying to get production owner accounts locked down to the dba
> group and not opened to vendor with turn-over and staffing issues. Today I
> spent a good deal of time (less time next time) tracing a performance
> emergency to one invalid package body. I have some questions:
>
> -Is the last_ddl_time in user_objects the definitive time in which the
> package could have become invalid?
>
> -Is there anyway for sure I can tell who, how, when a package became
> invalid?
>
> -The developer is saying that it relates to the size of the shared pool.
> Can the shared pool being "too" small cause a database object to just "go"
> invalid. I never heard of such a thing.
>
> Thanks,
> Paula
>
> ________________________________
> Low, Low, Low Rates! Check out Yahoo! Messenger's cheap PC-to-Phone call
> rates.
>
>

-- 
Best regards,
Alex Gorbachev

The Pythian Group
Sr. Oracle DBA

http://www.pythian.com/blogs/author/alex/
http://blog.oracloid.com
--
http://www.freelists.org/webpage/oracle-l