Search code examples
plsqloracle-aq

How to delete (drop) an Oracle AQ queue?


I wish to drop an Oracle AQ queue and the table associated with it.

What are the commands needed to perform this operation?


Solution

  • In order to delete the queue and the table associated with it, the steps are:

    1. Stop the queue
    2. Drop the queue
    3. Drop the queue table (optional)

    The following command will remove the queue and table.

    BEGIN
      DBMS_AQADM.STOP_QUEUE(queue_name => 'QUEUE_NAME');
      DBMS_AQADM.DROP_QUEUE(queue_name => 'QUEUE_NAME');
      DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'QUEUE_TABLE_NAME');
    END;
    

    You don't need to drop the table, but it is usually the case when executing this operation.