Re: Shared Pool causing packages to automatically go invalid!

  • From: Mindaugas Navickas <mnavickas@xxxxxxxxx>
  • To: paulastankus@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 2 Nov 2006 07:25:07 -0800 (PST)

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@xxxxxxxxx>
To: oracle-l <oracle-l@xxxxxxxxxxxxx>
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

Other related posts: