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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 02 2006 - 09:25:07 CST
