RE: packages becoming invalid at random
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 29 Sep 2009 11:34:21 -0700 (PDT)
> If you perform DDL against an object then other objects/code
> dependent on that object potentially go invalid. With 11g
> Oracle has improved the level of granularity for dependency checking
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
