Database : Oacle 220.127.116.11 Standard edition
OS : Windows Server 2008 R2 64x
At this morning, our application got this error message.
"Can not change status:ORA-04029: error ORA-7445 occurred when querying Fixed Table/View ORA-06512: at "SYS.DBMS_ALERT", line 78 ORA-06512: at "SYS.DBMS_ALERT", line 102 ORA-06512: at "LIMS.UTILITY_PKG", line 254 ORA-06512: at line 2"
When I got this error message from our BA, I had no idea about the DBMS_ALERT package. But after reviewing the DBMS_ALERT part in Oracle database manual, I guessed that DBMS_ALERT package uses a kind of message queue, and the queue has been pending status in some unknown reason.
So I tried to clear the pending status message with the following command;
SQL> EXEC DBMS_ALERT.REMOVEALL;
SQL> EXEC DBMS_ALERT.REMOVE(' <Name of the Alert>');
You can find the name of the alert using this command;
SQL> SELECT * FROM DBMS_ALERT_INFO;
Then, this issue was resolved!