This discussion is archived
1 Reply Latest reply: Sep 21, 2012 1:10 AM by 758358 RSS

Need details on tablespace and storage used by oracel queue

949968 Newbie
Currently Being Moderated
Hi All,

I am working on Oracle queues. I need some details regarding storage space of queues.

1. Is it mandatory that we must have seperate table space for queue? What will happen if we run out of table space? Do we loose the data?
2. How much storage is required for an oracle queue?

Please suggest me on this.
  • 1. Re: Need details on tablespace and storage used by oracel queue
    758358 Pro
    Currently Being Moderated
    Hi,
    946965 wrote:
    1. Is it mandatory that we must have seperate table space for queue? What will happen if we run out of table space? Do we loose the data?
    No - there is no need to separate out the queue objects, you might want to do this purely for simplicity of space management though, but it certainly isn't a requirement. Space running out in a tablespace for a queue is no different to space running out for a normal process - Oracle will trigger an error ("ORA-xxxxx unable to extend ... ") and the process trying to NQ on to the queue will fail or do whatever it has been coded to do in the event of an error. So, if your NQ code does something silly on error then I guess it is possible to lose data in the event of any error (not just space exhausted) but in general I would expect the process to report the error and stop.
    946965 wrote:
    2. How much storage is required for an oracle queue?
    This is entirely dependent on how you intend to use the queues, generally queues are not intended for long term data storage, they don't perform well for this - they work best with message in | message out (enqueue | dequeue) so unless you are intending on leaving messages in the queue for lengthy periods of time I would not anticipate large space consumption. But, only you guys can really work this out depending on the user data type and how long messages will stay in the queue.

    There are some useful notes on My Oracle Support about managing the space occupied by queues which is very important for performance, e.g.
    How do I reduce the High Watermark (HWM) of Advanced Queueing objects? (Doc ID 421474.1)

    Hope this helps,

    Paul

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points