Tuesday, May 29, 2018

ORA-04068 Errors from Oracle Packages

The following queries can useful when investigating ORA-4068 errors that will not clear.


To check the timestamp associated with a particular package / procedure / function run the below query:
alter session set nls_date_format='DD-MM-YYYY HH24:MI:SS';


Column OBJECT_NAME format a15
Column OBJECT_TYPE format a15
Column OWNER format a15
Column STATUS format a8
SELECT object_name, object_type, owner, status, last_ddl_time FROM
 dba_objects WHERE object_name = '&OBJECT';

 To check the timestamp for dependant packages:
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 =
'&OBJECT' );

For situations where you suspect a dependency check could be faulty:


alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
SELECT du.name duname,do.name dname, pu.name puname, po.name pname, p_timestamp, po.stime p_stime
 FROM sys.obj$ do, sys.dependency$ d, sys.obj$ po,sys.user$ du,sys.user$ pu
 WHERE p_obj#=po.obj#(+)
 AND d_obj#=do.obj#
 AND do.owner# = du.user#
 AND po.owner# = pu.user#
 AND do.status=1 /*dependent is valid*/
 AND po.status=1 /*parent is valid*/
 AND po.stime!=p_timestamp /*parent timestamp does not match*/
 AND do.type# not in (13,28,29,30) /*dependent type is not a type or java*/
 AND po.type# not in (13,28,29,30) /*parent type is not a type or java*/
 ORDER BY 4,2;

The above query  should not return any rows. If it does then both items referred to should be recompiled.


 - using  ALTER PACKAGE <package_name> COMPILE REUSE SETTINGS;
 - using  ALTER PACKAGE <package_name> COMPILE;

To check for details of why a package will not recompile, check to see if there were any errors reported in the last compilation:

SELECT name, type, text FROM dba_errors WHERE name = '&OBJECT';

References:
ORA-04068 Errors from User-Written and Oracle Packages (Doc ID 1161225.1)
ORA-4068 Errors for Valid Objects (Doc ID 835792.1)