Re: Shared Pool causing packages to automatically go invalid!
- From: A Joshi <ajoshi977@xxxxxxxxx>
- To: mnavickas@xxxxxxxxx, paulastankus@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Nov 2006 08:03:54 -0800 (PST)
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:
