How to stop & start a queue in Oracle AQ
What are the commands that I have to use to STOP or START an Oracle AQ Queue?
I need to STOP the queue in order to perform maintenance and analysis, and later START the queue once the analysis is complete.
Answer
You use the package DBMS_AQADM.
To STOP a queue the command looks like this:
BEGIN
DBMS_AQADM.STOP_QUEUE(queue_name => 'QUEUE_NAME');
END;
The optional paremeter "wait" for this procedure has the following behavior:
Specifies whether to wait for the completion of outstanding transactions. TRUE means wait if there are any outstanding transactions. In this state no new transactions are allowed to enqueue to or dequeue from this queue. FALSE means return immediately either with a success or an error.
And to START a queue it looks like this:
BEGIN
DBMS_AQADM.START_QUEUE(queue_name => 'QUEUE_NAME');
END;
You may have to add "SYS" as prefix for the package, depending on your permissions.