Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Delete from a queue table?

Re: Delete from a queue table?

From: Martic Zoran <>
Date: Sat, 22 Jan 2005 01:20:02 -0800 (PST)
Message-ID: <>

Hi Robert,

The direct deletion from the table is unsupported from Oracle, but the strange thing is that Oracle method to get rid of messages is very simple based on the background process removing them on the time bases (or maybe something else that I cannot remember now).

I am removing them directly with delete without problems when needed. Possible problem can arise if somebody is using it at the moment, then you will cause some strange exceptions to the app. I have never experienced a big problem by deleting them directly.

Now the coneptual question is:
- why do you need to delete the message at all in your case, you can read the message and enqueue it again. What is the problem of having 2 same messages in the different point in time for the history reason.

What you want is to change the history :)

Then you can delete them automatically based on some interval (1 year or whatever).

It is better to design it that you do not need manual interventions on the queue. Bear in mind that you have many options by using exception queues, multiple consumers (all need to read the message to be dequeued), ...
The only case I am not sure how to be covered is: you want your queue to not be crazy big to be in memory for performance reason, but you need the history for many years.
Do not know the Oracle story how to keep the history from the queue table except by manual method where you are reading the messages then putting them into another history table.
I suppose insert from select from the queue table will be much faster.


> Hi all,
>, AIX 5.2
> One of our developers is writing an app using
> Advanced Queuing. As a
> DBA, I'm trying to help him through some issues, but
> we're both AQ
> novices. The documentation and examples can be a
> little confusing for
> the newbie.
> The app is designed to leave a message in the queue
> table after it has
> been dequeued. That design was chosen so that the
> data can be queried
> for history/statistics.
> It turns out that there are times that a message
> needs to be removed
> from the queue table after it's been dequeued and
> re-enqueued so that it
> can be processed again. Any suggestions on how to
> remove it from the
> queue table? Can it just be deleted from the queue
> table?
> Bob Stauffer
> D&E Communications
> Ephrata, PA, USA
> 717-738-8737
> This e-mail message and any files transmitted with
> it are intended for the =
> use of the individual or entity to which they are
> addressed and may contain=
> information that is privileged, proprietary and
> confidential. If you are n=
> ot the intended recipient, you may not use, copy or
> disclose to anyone the =
> message or any information contained in the message.
> If you have received t=
> his communication in error, please notify the sender
> and delete this e-mail=
> message. The contents do not represent the opinion
> of D&E except to the ex=
> tent that it relates to their official business.
> --

Do you Yahoo!?
All your favorites on one personal page Try My Yahoo!
Received on Sat Jan 22 2005 - 04:23:16 CST

Original text of this message