garden1.jpg
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.

Latest Blog Items

email

Thank you for your interest in idbasolutions.

Contact us using this email: contact.us@idbasolutions.com