DB Version: Oracle Database 12cR2 EE on Exadata
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
WHEN OTHERS THEN ROLLBACK;
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?