Re: EXPIRED messages in the WF_NOTIFICATION_OUT queue

  • From: "Jurijs Velikanovs" <j.velikanovs@xxxxxxxxx>
  • To: ora-apps-dba@xxxxxxxxxxxxx
  • Date: Fri, 16 Jun 2006 13:15:32 +0100

Hi Kent,

Thank you for a quick response.
"retention" in the dba_queues  - indicates how long Oracle have to
keep a processed message in the queue table before actually delete it.
Default value for the WF_NOTIFICATION_OUT queue is 86400 = 24h.
It is fine and clear for me.

Presumably I haven't defined my original question clear enough. Sorry for that.
I trying to understood why some messages that suppose to be sent by
Notification Mailer (and are in OPEN, MAIL status in the
WF_NOTIFICATIONS table) are in EXPIRED status.
And end of the day are not sent to end users.

What would you say?
Yury



On 6/16/06, Rolfe, Kent (GTI AIS OPS/SE) <kent_rolfe@xxxxxx> wrote: 
Look up the definition of "retention" in the dba_queues  - i.e. desc
dba_queues and you will see a column - look it up in the manual and it
should give you your answer.

-----Original Message-----
From: ora-apps-dba-bounce@xxxxxxxxxxxxx
[mailto:ora-apps-dba-bounce@xxxxxxxxxxxxx] On Behalf Of Jurijs
Velikanovs
Sent: 16 June 2006 12:54
To: ora-apps-dba@xxxxxxxxxxxxx
Subject: EXPIRED messages in the WF_NOTIFICATION_OUT queue


Hello team,

Please take a look on the following output.
There are 795 messages in the WF_NOTIFICATION_OUT queue in our LIVE
system.
Some of them have been put in the queue just today.
I wonder how messages in that queue become EXPIRED?
Is anybody experienced the same behavior?
Those messages are not dequeued and are not sent to end users.
What would be the right method to troubleshoot those type of messages?

Thank you in advance,
Yury

APPS:LIVE> alter session set nls_date_format='YYYY.MM.DD HH24:MI:SS';

Session altered.

APPS:LIVE>
APPS:LIVE> SELECT msg_state, count(*)
APPS:LIVE> FROM   applsys.aq$wf_notification_out
APPS:LIVE> group by msg_state;

MSG_STATE       COUNT(*)
------------- ----------
EXPIRED              795
PROCESSED            633

APPS:LIVE>
APPS:LIVE> SELECT o.enq_time
APPS:LIVE> FROM   applsys.aq$wf_notification_out o
APPS:LIVE> where o.msg_state = 'EXPIRED'
APPS:LIVE> and trunc(o.enq_time) = trunc(sysdate);

ENQ_TIME
-------------------
2006.06.16 00:46:15
2006.06.16 00:46:15
2006.06.16 00:46:15

APPS:LIVE>

--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html
--------------------------------------------------------

If you are not an intended recipient of this e-mail, please notify the sender, 
delete it and do not read, act upon, print, disclose, copy, retain or 
redistribute it. Click here for important additional terms relating to this 
e-mail.     http://www.ml.com/email_terms/
--------------------------------------------------------




--
Yury
+44 7738 013090 (GMT)
============================================
http://otn.oracle.com/ocm/jvelikanovs.html

Other related posts:

  1. Home
  2. ora-apps-dba
  3. Archive
  4. 06-2006