I tried to stop the queue using "dbms_aqadm.stop_queue" procedure. It is failing with errors,
ERROR at line 1:
ORA-24018: STOP_QUEUE on APPLSYS.WF_NOTIFICATION_OUT failed, outstanding
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4921
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 1
If I give wait => TRUE parameter to "dbms_aqadm.stop_queue" procedure, it is hanging forever. I wanted to stop, drop and recreate the queue as the notification count in wf_notification_out increased to over 500.
bash-3.2$ oerr ora 24018
24018, 00000, "STOP_QUEUE on %s failed, outstanding transactions found"
// *Cause: There were outstanding transactions on the queue, and WAIT was set to
// false, so STOP_QUEUE was unsucessful in stopping the queue.
// *Action: Set WAIT to TRUE and try STOP_QUEUE again. It will hang till all
// outstanding transactions are completed.
Please check the below note and respective database related bugs mentioned in the documents..
OERR: ORA-24018 "STOP_QUEUE on %s failed, outstanding transactions found" Reference Note (Doc ID 49602.1)