3

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.

Evandro Pomatti
  • 13,341
  • 16
  • 97
  • 165

2 Answers2

6

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.

Evandro Pomatti
  • 13,341
  • 16
  • 97
  • 165
  • you created question, at the same moment created answer and marked it as correct? – Alexo Po. Sep 28 '21 at 11:54
  • @AlexoPo. actually, I take the time to write a question, formulate a well-written answer that works, post it on SO for you to search on google and just use it, and you dare to complain? – Evandro Pomatti Sep 28 '21 at 18:16
  • First link in google usually leads to oracle docs of DBMS_AQADM, so - yes, i dare. – Alexo Po. Sep 29 '21 at 08:00
3

Often I find that I want to stop only dequeue. Then I have to use the extra options on START_QUEUE() like so

execute DBMS_AQADM.STOP_QUEUE (queue_name => 'schema.queue', enqueue => FALSE, dequeue => TRUE);
anders.norgaard
  • 1,062
  • 13
  • 23