Re: Delete from a queue table?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: rstauffer@xxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 22 Jan 2005 01:20:02 -0800 (PST)
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.
Regards,
Zoran
--- "Stauffer, Robert G"
<rstauffer@xxxxxxxxxxxxxxxxxxxx> wrote:
> Hi all,
>
> 9.2.0.5, 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
> DBA
> D&E Communications
> Ephrata, PA, USA
> 717-738-8737
> rstauffer@xxxxxxxxxxxxxxxxxxxx
>
>
>
> **DISCLAIMER
> 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.
> --
> //www.freelists.org/webpage/oracle-l
>
__________________________________
Do you Yahoo!?
All your favorites on one personal page ? Try My Yahoo!
http://my.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
