Linux AS 5.3
中午时间没有任何objecct被改动的情况下， 一个存储过程忽然报错，
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "SP_A
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
程序员也没有权限去修改任何东西啊， 奇怪.
metalink 查询到的信息， 真是不懂 Recompilation of a view 是什么意思 ？ 我也没有编译过view,
procedure也没有使用过view .
Applies to: Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.
SymptomsWhen loading certain PL/SQL packages the database continuously reports ORA-04068 errors.
Restarting database does not help. Flushing shared pool does not help.
We can see timestamp discrepancies exist for valid objects.
set pagesize 10000
column d_name format a20
column p_name format a20
select do.obj# d_obj,do.name d_name, do.type# d_type,
po.obj# p_obj,po.name p_name,
to_char(p_timestamp,'DD-MON-YYYY HH24:MI:SS') "P_Timestamp",
to_char(po.stime ,'DD-MON-YYYY HH24:MI:SS') "STIME",
decode(sign(po.stime-p_timestamp),0,'SAME','*DIFFER*') X
from sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and do.status=1 /*dependent is valid*/
and po.status=1 /*parent is valid*/
and po.stime!=p_timestamp /*parent timestamp not match*/
order by 2,1;
Output would look like:
RADAR_SALES_ORDER_API 11 VEHICLE_STOCK_API 08-JUN-2007 13:27:06 15-JUN-2007 19:03:05 *DIFFER*
SALES_SALES_OBJ_ITEMS_API 11 SALES_BUSINESS_DEALS_API 15-JUN-2007 18:45:09 15-JUN-2007 19:03:07 *DIFFER*
Also the problematic packages/procedures reported in the error exists and have status VALID in dba_objects view.
CauseThe cause of this problem has been identified and verified in Bug 6136074 ORA-4068 ORA-4065 ORA-06508 ERRORS SIGNALED FOR VALID OBJECTS
Note: Recompilation of a view might lead to inconsistent timestamps for some of the view's PLSQL dependents.
If you see the above errors for packages/procedures which are in VALID state then you are hitting this bug.
SolutionSolution
-----------
Workaround #1
=============
1. Recompile the parent view.
2. Check the list of objects with timestamp mismatch using the SQL referred above.
3. Recompile those objects in point #2.
Workaround #2
============
Download and apply the Patch 6136074 to *prevent* this issue in the future.
Apply the patch for Bug 6136074.
For AIX 5L based system a Merge Label Request was created on top of 10.2.0.3 for bugs Unpublished Bug 5901891 and Bug 6136074. Download and apply Merge Patch for Bug 6164148 which contains the fix for the Base Bug 6136074.
Note : Objects which has * timestamp mismatch * before applying the patch should be compiled manually.
errormessage.JPG