隐含参数_trace_kqlidp可以定位procedure,function等对象的失效原因, 在11.2.04上需要先patch 4991675

[oracle@node1 4991675]$ opatch lsinv
Oracle Interim Patch Installer version 11.2.0.3.4
Copyright (c) 2012, Oracle Corporation.  All rights reserved.

Oracle Home       : /oracle/app/oracle/product/11.2.0.4
Central Inventory : /oracle/app/oraInventory
   from           : /oracle/app/oracle/product/11.2.0.4/oraInst.loc
OPatch version    : 11.2.0.3.4
OUI version       : 11.2.0.4.0
Log file location : /oracle/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/opatch2015-09-21_12-00-43PM_1.log

Lsinventory Output file location : /oracle/app/oracle/product/11.2.0.4/cfgtoollogs/opatch/lsinv/lsinventory2015-09-21_12-00-43PM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (1): 
Oracle Database 11g                                                  11.2.0.4.0
There are 1 products installed in this Oracle Home.
Interim patches (1) :
Patch  4991675      : applied on Mon Sep 21 11:59:42 CST 2015
Unique Patch ID:  18126143
   Created on 8 Oct 2014, 18:59:32 hrs PST8PDT
   Bugs fixed:
     4991675
Rac system comprising of multiple nodes
  Local node = node1
  Remote node = node2
--------------------------------------------------------------------------------


create table t(id number);

create or replace procedure p
is
begin
  for rec in (
    select * from t)
  loop
    null;
  end loop;
end;
/

alter session set "_trace_kqlidp"=true;

alter table t add name varchar2(10);

看看效果:
1.alert 
INVALIDATION performed by ospid=30578. Please see tracefile.

2.session trace 
INVALIDATION: Current SQL follows
alter table t add name varchar2(10)
ksedsts()+465<-kqlidp0()+21903<-atbdrv()+10505<-opiexe()+21993<-opiosq0()+3932<-kpooprx()+274<-kpoal8()+842<-opiodr()+917<-ttcpip()+2183<-opitsk()+1710<-opiino()+969<-opiodr()+917<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+265<-main()+201<-__libc_start_main()+244
Fine-grain delta dump for unit SYS.T
- Change bit vector 0:
  -23, -16,
- Change bit vector 1:Empty
- Change bit vector 2:Empty
- Shift table:Empty
kqlidp0: 88790 (CURSOR SYS.T) (Parent:    0) [ROOT]
kqlidp0:. 88791 (PROCEDURE SYS.P) (Parent:88790) [ADDED TO QUEUE]
kqlidp0:. 88791 (PROCEDURE SYS.P) (Parent:88790) [INVALIDATE]