Re: Shared Pool causing packages to automatically go invalid!
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: paulastankus@xxxxxxxxx
- Date: Tue, 7 Nov 2006 14:04:28 -0800
On 11/1/06, Paula Stankus <paulastankus@xxxxxxxxx> wrote:
-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.
Paula,
The attached SQL file will show all dependencies for an object,
both forwards and backwards through the heirarchy of objects
in DBA_DEPENDENCIES, along with the LAST_DDL_TIME of all children.
The LAST_DDL_TIME should help track down why an object became invalid.
I'm fairly sure this query will not work in 8i. :(
You can circumvent that by logging into some account other than SYS
and creating a table DBA_DEPENDENCIES. (it doesn't work in 8i due
to queries on complex views and 'connect by' not working)
create table scott.dba_dependencies
as
select * from sys.dba_dependencies.
HTH,
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
-- dba_dependencies.sql
-- jkstill@xxxxxxxxx
-- base query from Jacques Kilchoer
-- 11/07/2006 - jkstill - added no_merge hints
-- encapsulated into inline view
-- added 'level'
-- display child DDL time
--
-- call on the command line:
-- @dba_dependencies <OWNER> <OBJECT>
-- if not on the command line, user will be
-- prompted for values
@clears
@columns
prompt Dependencies for Owner?:
col cowner noprint new_value uowner
set term off feed off
select upper('&1') cowner from dual;
set term on feed on
prompt Dependencies for Object?:
col cobject noprint new_value uobject
set term off feed off
select upper('&2') cobject from dual;
set term on feed on
set line 142 pages 60
column display_parent format a58
column display_child format a58
column referenced_owner noprint
column referenced_object noprint
column referenced_type noprint
column owner noprint
column object noprint
column type noprint
column last_ddl_time format a22 head 'CHILD DDL TIME'
undef 1 2
with dependencies as (
-- top down through the heirarchy
select /*+ no_merge */
referenced_type || ' "' || referenced_owner || '"."' ||
referenced_name || '"' as parent,
type || ' "' || owner || '"."' || name || '"' as child,
level hlevel,
referenced_owner, referenced_name, referenced_type,
owner, name, type
from dba_dependencies
start with
referenced_owner = '&&uowner'
and referenced_name = '&&uobject'
connect by
referenced_owner = prior owner
and referenced_name = prior name
and referenced_type = prior type
union
-- bottom up through the heirarchy
select /*+ no_merge */
referenced_type || ' "' || referenced_owner || '"."' ||
referenced_name || '"' as parent,
type || ' "' || owner || '"."' || name || '"' as child,
level hlevel,
referenced_owner, referenced_name, referenced_type,
owner, name, type
from dba_dependencies
start with
owner = '&&uowner'
and name = '&&uobject'
connect by
owner = prior referenced_owner
and name = prior referenced_name
and type = prior referenced_type
order by 1, 2
)
select lpad(' ',2*d.hlevel,' ') || d.parent display_parent, d.child
display_child, o.last_ddl_time
from dependencies d, dba_objects o
where o.owner = d.owner
and o.object_type = d.type
and d.name = o.object_name
order by parent, child
/
Other related posts:
