RE: packages becoming invalid at random

To add to what Mark said, only those DDL's that change object specification time, i.e. dba_objects.timestamp, not last_ddl_time, invalidate dependent objects. For example, "alter table move" or "grant" won't update the specification time and so won't invalidate views using the table. But "alter table modify" that modifies a column to exactly the same column type (so the specification in effect remains unchanged) updates the specification time, and therefore invalidates the dependent objects.

11g's fine grained dependency is great. For example, if view V or package P uses column C of table T, you can add column C2 to T without invalidating V or P. Just for a record, if you ever need to disable this behavior perhaps for research or troubleshooting, you can set _IGNORE_FG_DEPS to NONE.

To answer Lyall's question, why not find all "parent" objects the invalid package uses and check their timestamps in dba_objects? Then go to dba_audit_trail to find the DDL SQL on the object around that time. This assumes you have audit_trail turned on and you issued appropriate "audit" command earlier ("audit procedure", "audit table", "audit alter table"). If audit_trail is set to db,extended, dba_audit_trail has the complete SQL in sql_text column in addition to the abbreviated text under action_name.

Yong Huang       

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