Oracle Advanced Queue: Dequeue not working, stuck in WAIT state, DELAY_TIMESTAMP - SYSTIMESTAMP > 0

The queue's view DELAY_TIMESTAMP column appears to have the correct time, but when compared to SYSTIMESTAMP, there's an unexpected five hour difference.   After the message should have gone to the READY state, it is still in the WAIT state.

For example, a message is enqueued using DBMS_AQ.ENQUEUE with delay of 60 seconds.  I wait till well after the message should be dequeued.  The message is still in the queue despite attempting to dequeue it with DBMS_AQ.DEQUEUE.  Here is what the query looks like.

select delay_timestamp, systimestamp , delay_timestamp - systimestamp
from AQ$THE_QUEUE;
DELAY_TIMESTAMP =                     7/1/2009 8:23:05.769546 PM
SYSTIMESTAMP =                            7/1/2009 9:06:23.944861 PM +00:00
DELAY_TIMESTAMP-SYSTIMESTAMP   +00 04:16:41.824685

The difference should be negative because the time should have expired.  However, it's positive, exactly 5 hours less the time since it was enqueued.

This code worked fine until it was re-installed as part of dropping and creating the Oracle user.  It works fine on Oracle XE and other databases.  The problem is consistent with some change having been done either in Oracle or the Unix operating system.  I don't have control over those systems and the administrator says nothing changed.  Oracle has been stopped and started since the problem was noticed.  The queue table was then dropped and recreated.

Do you have any idea as to what could cause the difference?  It looks like a difference between GMT and CDT time zones.  It could be related to the session time zone.

It is Oracle 10g 10.2.0.4.0.

Thank you...

w_enqueue_options    dbms_aq.enqueue_options_t;
   w_message_handle     RAW(16);
   w_message_properties dbms_aq.message_properties_t;
BEGIN
   w_obj := NEW obj();
   w_message_properties.delay := 60;
 
   dbms_aq.enqueue(queue_name => 'AQ.THE_QUEUE',
                   enqueue_options => w_enqueue_options,
                   message_properties => w_message_properties,
                   payload => w_obj,
                   msgid => w_message_handle);
 
END;

Open in new window

EdFredAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anand_2000vCommented:
what about the code to dequeue?
0
EdFredAuthor Commented:
Thanks for the reply.

Here's the DEQUEUE code.  I do not expect DEQUEUE to retrieve any data because the because the message MSG_STATE is WAIT.

But it is moot.  I just decided to not use Oracle AQ.  It's has a lot of neat features and works with the many tests I've run on it.  But, for me, it has not been as stable as regular tables.

It's not worth investigating anymore.
-Ed
   w_dequeue_options    DBMS_AQ.DEQUEUE_OPTIONS_T;
   w_message_handle     RAW(16);
   w_message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
BEGIN
   w_dequeue_options.wait := 30;
   --in_filter is an input arg
   w_dequeue_options.deq_condition :='tab.user_data.filter='''||in_filter||'''';
 
   DBMS_AQ.DEQUEUE(queue_name => 'AQ.THE_QUEUE',
                   dequeue_options => w_dequeue_options,
                   message_properties => w_message_properties,
                   payload => w_obj,   --SP instantiates v_message
                   msgid => w_message_handle);

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.