Re: Shared Pool causing packages to automatically go invalid!

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@xxxxxxxxx> 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@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
    
---------------------------------
  




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

Other related posts: