Sunday, February 8, 2009

ORA-06508, ORA-04065, ORA-04068: existing state of packages has been discarded

Problem description
While executing a stored procedure the following error stack is returned.

ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04065: not executed, altered or dropped stored procedure "A.RATEDUMMY"
ORA-06508: PL/SQL: could not find program unit being called: "A.RATEDUMMY"
ORA-06512: at "A.AMT_OR_TIME", line 128

Cause of the Problem
1)The most possible cause of ORA-04068 is a dependent object on the procedure/package/function was altered by a DDL statement.

When a package is compiled, all copies of the functions/packages reside on the shared pool marked as invalid. Now if you invoke the function/package then it sees that flag is now set and need to get a new copy and instantly ORA-04068: existing state of packages has been discarded error comes out as current copy of packages reside on the shared pool must be thrown out.

2)Using DDL (Drop, ALTER or CREATE) whenever you recompile or change the package then dependent package become invalid through cascade invalidation.

3)If a package/function is dependent on another package/function where there is no body
on the dependent package/function, then while execution error will return. This error invalidates the package.

4)If a remote dependent object has been altered through a DDL statement then invoking the procedure/package can result ORA-04068: existing state of packages has been discarded .

Checks need to do
Check the package last compile time,
SQL> col object_name for a20
SQL> set linesize 140
SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';

Session altered.

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
dba_objects WHERE object_name = 'CREDIT_AMT_OR_TIME';

OBJECT_NAME OBJECT_TYPE OWNER STATUS LAST_DDL_TIME
-------------------- ------------------- ------------------------------ ------- ------------------
AMT_OR_TIME FUNCTION MAXIMSG VALID 09-FEB-09 12:04:08

Check the dependent object's last alteration by,

SQL> SELECT object_name, object_type, owner, status, last_ddl_time FROM
dba_objects WHERE ( object_name, object_type ) IN ( SELECT
referenced_name, referenced_type FROM dba_dependencies WHERE name =
'AMT_OR_TIME' );

OBJECT_NAME OBJECT_TYPE OWNER STATUS LAST_DDL_
------------------------- ------------------- ------------------------------ ------- ---------
DUAL SYNONYM PUBLIC VALID 30-AUG-05
DBMS_OUTPUT SYNONYM PUBLIC VALID 30-AUG-05
RATECHART TABLE MAXIMSG VALID 04-DEC-08
SUBSCRIBERS TABLE MAXIMSG VALID 04-DEC-08
REGISTERED_PHONES TABLE MAXIMSG VALID 04-DEC-08
RATEDUMMY FUNCTION MAXIMSG VALID 09-FEB-09
ACCESS TABLE MAXIMSG VALID 04-DEC-08
SPEEDDIAL TABLE MAXIMSG VALID 04-DEC-08


Check the existing errors on package by,

SQL> SELECT name, type, text FROM dba_errors WHERE name = 'AMT_OR_TIME';


Solution of the Problem
1)Re-execute the package possibly will solve the problem.
2)Recompile the procedure/function/package and it's dependent.
ALTER PACKAGE package_name compile;
ALTER FUNCTION function_name compile;
3)If the dependent objects in other schema then note that explicitly execute permission is granted to the user.

1 comment: