Oracle 12cR2 RAC - ORA-06508

Question

Environment:

• DB Version: Oracle Database 12cR2 EE on Exadata

• RAC: True

• Problem: ORA-06508 on procedure.

One of our users had a DBA role privilege, we don't want such a big privilege to be defined anymore. When we revoke the DBA role privilege from this user, all procedures, packages and triggers became invalid. This was something we expected at that moment and we accepted it as normal. When we compiled all of them, there was no problem and we reassigned the DBA role privilege to this user in order to postpone this work until later.

The problem starts from this point. We get an error when one of the procedures wants to run. But the error is not constantly getting, for example it was working correctly for the last 26 hours, but then it got the error. When we drop / create or compile, the problem is solved. However, the strange part is that when this problem occurs, the procedure and dependencies all seem to be valid. The error is as follows:

Error raised in: PROCEDURE_NAME at line xxxx - ORA-06508: PL/SQL: could not find program unit being called

Line-xxxx:

EXCEPTION
   WHEN OTHERS THEN ROLLBACK;
    R_CODE:= 400;
    R_MESSAGE:='Error raised in: '|| $$plsql_unit ||' at line ' || $$plsql_line || ' - '||sqlerrm;

I could not find a suitable solution for this scenario on Oracle Support or on dba.stackexchange and I am still researching. Anyone have a good idea?

Best Regards,

Answer 1

$$plsql_line contains the line where it appears, which is useless locating the cause of an exception. You're not the first falling for this, eg. using-plsql-line. Read the solution there, eg. using DBMS_UTILITY.FORMAT_CALL_STACK in your exception handling.

Answer 2

Reason: The called stored procedure has been modified
solution:Recompile stored procedures