Re: Shared Pool causing packages to automatically go invalid!

On 11/1/06, Paula Stankus <paulastankus_at_yahoo.com> 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




--
http://www.freelists.org/webpage/oracle-l




text/x-sql attachment: dba_dependencies.sql