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