Re: Shared Pool causing packages to automatically go invalid!

From: A Joshi <ajoshi977_at_yahoo.com>
Date: Thu, 2 Nov 2006 08:03:54 -0800 (PST)
Message-ID: <20061102160354.74204.qmail@web58008.mail.re3.yahoo.com>

Paula,

I agree with below. If it is still invalid you can try to trace from the compilation error. Tracing to referenced objects as suggested below. I am sure all of us have heard 'dog ate my shared pool' stories from others including other DBAs and especially mgmt with sql access.

Changes to access privileges can also cause invalid objects. In cases like some other owner's referenced object is invalidated due to access or recreated (drop and recreate).

Mindaugas Navickas <mnavickas_at_yahoo.com> wrote:

Hi Paula,

Here some answers - I do not have 8i installed, so I tried on 9i:

-Is the last_ddl_time in user_objects the definitive time in which the package could have become invalid?
NO. If package gets invalidated, last_ddl_time shows last recompilation time (time when package became valid).

-Is there anyway for sure I can tell who, how, when a package became invalid?
It's makes more sense to look into underlying objects - may be somebody is doing alter table... to set/reset constraint. This definetly will cause package invalidation. My understanding was that even truncate should invalidate package, however I can not see it happening on 9i.

-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.

I never heard about that as well.

Look into objects that that package has dependency on: select p.object_name, p.object_type, p.created, p.last_ddl_time, p.status, d.owner, d.object_name, d.object_type, d.created, d.last_ddl_time, d.status from user_objects p, dba_objects d, user_dependencies x where p.object_name = 'TEST_ME' -- here is your package name

  and p.object_name = x.name
  and x.referenced_owner = d.owner
  and x.referenced_name = d.object_name
  and x.referenced_type = d.object_type;

Are there db links involved among dependencies?

Regards
Mindaugas

Original Message ---- From: Paula Stankus <paulastankus_at_yahoo.com> To: oracle-l <oracle-l_at_freelists.org> Sent: Wednesday, November 1, 2006 4:46:38 PM Subject: Shared Pool causing packages to automatically go invalid!

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

We have the perfect Group for you. Check out the handy changes to Yahoo! Groups.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Nov 02 2006 - 10:03:54 CST