RE: source of package invalidation

-----Original Message-----
From: Chandra Pabba [mailto:Chandra_Pabba_at_verizon.net] Sent: Saturday, March 14, 2009 11:02 AM
To: 'yong321_at_yahoo.com'; 'chris_stephens_at_admworld.com' Cc: 'oracle-l_at_freelists.org'
Subject: RE: source of package invalidation

Shiva,

Did you check to make sure that you can successfully SSH between the nodes (using both the Public and private interfaces) - without being prompted for password or seeing the banner.

Thanks
Chandra

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yong Huang
Sent: Friday, March 13, 2009 10:34 AM
To: chris_stephens_at_admworld.com
Cc: oracle-l_at_freelists.org
Subject: Re: source of package invalidation

> We are seeing packages invalidated in our production database
> and I don't have a clue how to track down the actual cause.
>
> I've taken a look at dba_dependencies and know the potential
> candidates for causing the invalidation but I'm looking to
> actually identify the chain of events that is causing this.

Chris,

Why not check latest last_ddl_time's?

select owner, object_name, object_type, last_ddl_time from dba_objects where last_ddl_time > sysdate - 1/12 order by 4;

Not all DDL's update the objects' last_ddl_time. See http://yong321.freeshell.org/oranotes/DDLsNotUpdatingLast_ddl_time.txt But then if they don't, they probably don't invalidate the dependent objects either.

Auditing helps too. I always turn on audit_trail when I build a new database. As far as I know, if you don't actually audit anything (type command audit <something>), there's really no overhead. But when you do need to audit something even temporarily, you don't need to schedule downtime to change that parameter. So in this case, you would just (I think) audit table, audit view,..., and wait for the PL/SQL error to appear one more time and noaudit ...

Yong Huang       

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


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