DBMS_ALERT error issue

by , under Oracle

Database : Oacle 11.2.0.4 Standard edition
OS : Windows Server 2008 R2 64x

At this morning, our application got this error message.

DBMS_ALERT_Issue
"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;

or

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!

 

Leave a Reply