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)
