This content has been marked as final. Show 1 reply
946965 wrote: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.
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?
946965 wrote: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.
2. How much storage is required for an oracle 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,