ORA-04068 04065 and 06508
Disclaimer: Apply to a TEST environment first. Use on production systems is at DBAs own risk.
Description of the problem:
Today: 08-Jun-09
We are getting the following errors from various parts of our Oracle PA module; such as unable to close your month end processing, can’t complete orders and unable to complete billing to send customers:
ORA-04068 existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512 Error
After opening a TAR with Oracle it looks like we are affected by bug described in NOTE:456338.1
Fix:
This following SQL from NOTE:456338.1 identifies objects that are depending on objects that have different timestamps, once I have the list then I recompile all of them listed and the problem is resolved.
set pagesize 10000
set linesize 150
column d_name format a35
column p_name format a35
select dbo.object_type, 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 dba_objects dbo, sys.obj$ do, sys.dependency$ d, sys.obj$ po
where P_OBJ#=po.obj#(+)
and D_OBJ#=do.obj#
and dbo.object_id=D_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 3,2
On 04-Jun-09 the sql identified on PROD the following object which I recompiled:
Please note that all these objects were already show status VALID on dba_objects. In fact there are currently no invalid objects in PROD.
alter package AP_WEB_EXPENSE_FORM compile;
alter package AP_WEB_EXPENSE_FORM compile body;
alter package AP_WEB_OA_MAINFLOW_PKG compile;
alter package AP_WEB_OA_MAINFLOW_PKG compile body;
alter package AP_WEB_OA_REPORTING_UTIL compile;
alter package AP_WEB_OA_REPORTING_UTIL compile body;
alter package AP_WEB_ACCTG_PKG compile;
alter package AP_WEB_ACCTG_PKG compile body;
alter package HXC_PROJ_MANAGER_APPROVAL_PKG compile;
alter package HXC_PROJ_MANAGER_APPROVAL_PKG compile body;
alter package HXC_PROJ_MANAGER_APPROVAL_PKG compile;
alter package HXC_PROJ_MANAGER_APPROVAL_PKG compile body;
alter package PA_CLIENT_EXTN_PROJ_STATUS compile;
alter package PA_CLIENT_EXTN_PROJ_STATUS compile body;
alter package PA_PROJECT_REQUEST_PVT compile;
alter package PA_PROJECT_REQUEST_PVT compile body;
alter package PA_CLIENT_EXTN_SST_DFF compile;
alter package PA_CLIENT_EXTN_SST_DFF compile body;
alter package PA_SELF_SERVICE_UTILS_PVT compile;
alter package PA_SELF_SERVICE_UTILS_PVT compile body;
alter package PA_PJI_UTIL_PKG compile;
alter package PA_PJI_UTIL_PKG compile body;
alter package PA_FP_ORG_FCST_UTILS compile;
alter package PA_FP_ORG_FCST_UTILS compile body;
alter package PNRX_LEASE_OPTIONS compile;
alter package PNRX_LEASE_OPTIONS compile body;
alter package PNP_UTIL_FUNC compile;
alter package PNP_UTIL_FUNC compile body;
alter package PNRX_MILESTONES compile;
alter package PNRX_MILESTONES compile body;
alter package PNRX_RENT_LES compile;
alter package PNRX_RENT_LES compile body;
alter package PNRX_SP_UTIL_BY_LEASE compile;
alter package PNRX_SP_UTIL_BY_LEASE compile body;
alter package PNRX_SP_UTIL_BY_LOC compile;
alter package PNRX_SP_UTIL_BY_LOC compile body;
alter package PN_CONTACT_ASSIGNMENTS_PKG compile;
alter package PN_CONTACT_ASSIGNMENTS_PKG compile body;
alter package PN_INSURANCE_REQUIREMENTS_PKG compile;
alter package PN_INSURANCE_REQUIREMENTS_PKG compile body;
alter package PN_LANDLORD_SERVICES_PKG compile;
alter package PN_LANDLORD_SERVICES_PKG compile body;
alter package PN_LEASE_DETAILS_PKG compile;
alter package PN_LEASE_DETAILS_PKG compile body;
alter package PN_OPTIONS_PKG compile;
alter package PN_OPTIONS_PKG compile body;
alter package PN_RIGHTS_PKG compile;
alter package PN_RIGHTS_PKG compile body;
alter package XSA_PA_RETENTION_PKG compile;
alter package XSA_PA_RETENTION_PKG compile body;
alter package XSA_PA_RETENTION_PKG compile;
alter package XSA_PA_RETENTION_PKG compile body;
alter package XSA_PA_SALES_TAX_COST_PKG compile;
alter package XSA_PA_SALES_TAX_COST_PKG compile body;
The problem has gone away and have not re-appeared since.