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 /