ORA-06508: PL/SQL: could not find program unit being called

I got ” ORA-06508: PL/SQL: could not find program unit being called ” error in Oracle database.

 

ORA-06508: PL/SQL: could not find program unit being called

 

Details of error are as follows.

ORA-06508: PL/SQL: could not find program unit being called.

Cause: An attempt was made to call a stored program that could not be found.
The program may have been dropped or incompatibly modified, or have compiled with errors.

Action: Check that all referenced programs, including their package bodies,
exist and are compatible.

 

 

PL/SQL: could not find program unit being called

This ORA-06508 errors are related with the stored procedure, the stored procedure you run could not be found.

 

The issue is caused by unpublished bug 7284151, which relates to a new feature introduced in 11g called fast validation. The purpose of  fast validation is to speed up recompilation by attempting to detect only those objects which strictly needed to be recompiled.

This issue is documented in Note 1192068.1 “Recreating An Existing Package Generates Timestamps Out Of Sync Which Causes ORA-6508”.

To check if you have timestamp discrepancies on your instance that are causing the error(s) run the
following SQL query.

   alter session set nls_date_format='dd-mon-yy hh24:mi:ss';

   select do.name dname, po.name pname, p_timestamp, po.stime p_stime
   from sys.obj$ do, sys.dependency$ d, sys.obj$ po
  where p_obj#=po.obj#(+)
  and d_obj#=do.obj#
  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 (28,29,30) /*dependent type is not java*/
  and po.type# not in (28,29,30) /*parent type is not java*/
  order by 2,1;

Check the output of the query for the package name found in your error. If this package is found then this is most likely the cause of the error(s).

Another sign of this issue is that the output of the Diagnostic: Apps Check program shows compiled versions of packages that are lower than what is found on the server.

 

To solve this error, Perform the following as SYSDBA:

1. ALTER SYSTEM set "_disable_fast_validate"=TRUE scope=spfile;

 

2. Shut down the database:

 

   SHUTDOWN IMMEDIATE

 

3. Start the database in upgrade mode:

   STARTUP UPGRADE

4. Oracle provides scripts in $ORACLE_HOME/rdbms/admin that, when run as sys, will invalidate and re-validate all PL/SQL objects so any timestamp mismatches should be resolved. The script utlirp.sql invalidates all PL/SQL based objects, recreates STANDARD and DBMS_STANDARD, invalidates all views and synonyms dependent on now invalid objects and then does some clean up. The script utlrp.sql calls UTL_RECOMP.RECOMP_PARALLEL which performs dependency based recompilation, in parallel where resources allow. Please use these two scripts:

a) Invalidate all the objects – utlirp.sql

b) Recompile all the objects – utlrp.sql

5. ALTER SYSTEM set "_disable_fast_validate"=FALSE scope=spfile;

 

6. Shut down the database:

 

   SHUTDOWN IMMEDIATE

 

7. Start the database:

   STARTUP

 

8. Retest the issue.

Note: One customer reported the above actions took about 8 hours. Manually recompiling the affected files shown in the output of the SQL query should work as well, but this will be a labor intensive process if the list is extensive.

 

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

See more