A quick cheat sheet for clearing out oracle database queues with DBMS_AQADM

To find the name of the queue table you might need to deal with you can simply do

SELECT * FROM all_queue_tables

Which will give you the name and owner of the queue table. Then you can do a select to find out how many items are in the queue. In the example below the queue table is called test_msg_queue_table and the owner is app_owner. Note the aq$ must be added to the start of the queue table name.

SELECT count(*) FROM app_owner.aq$test_msg_queue_table

Then as the schema owner you can run a script as below to clear out all the items in the queue. 

DECLARE
    po dbms_aqadm.aq$_purge_options_t;
BEGIN
    po.block := TRUE;
    DBMS_AQADM.PURGE_QUEUE_TABLE(
        queue_table => 'APP_OWNER.TEST_MSG_QUEUE_TABLE',
        purge_condition => NULL,
        purge_options => po);
END;