Cannot drop Oracle queue table with DBMS_AQADM.DROP_QUEUE_TABLE

Question

I'm trying to clean up an accidental installation of LOG4PLSQL into the wrong (i.e., SYS) schema. There is a queue table called QTAB_LOG that needs to go away. I have successfully stopped and dropped the associated queue:

call DBMS_AQADM.STOP_QUEUE('LOG_QUEUE');
call DBMS_AQADM.DROP_QUEUE('LOG_QUEUE');

But dropping the queue table itself fails:

call DBMS_AQADM.DROP_QUEUE_TABLE('QTAB_LOG');

with this error:

SQL Error: ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 1
00942. 00000 -  "table or view does not exist"

And of course dropping the table the normal way:

drop table QTAB_LOG;

is not allowed:

SQL Error: ORA-24005: Inappropriate utilities used to perform DDL on AQ table LOG4PLSQL.QTAB_LOG
24005. 00000 -  "must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables" 
*Cause:    An attempt was made to use the SQL command DROP TABLE for queue
           tables, but DROP TABLE is not supported for queue tables.
*Action:   Use the DBMS_AQADM.DROP_QUEUE_TABLE procedure instead of the
           DROP TABLE command.

What am I doing wrong?

Answer 1

Did you have any previous attempts at dropping the queue table that failed? This situation of an orphaned queue table is usually the result of some problem that resulted in an exception thrown when using the AQ API calls.

I don't know when this was introduced, but at least 11g now has a FORCE parameter to the drop_queue_table call that stops and drops the queues as part of the drop table process. In your case it's probably too late for that to work but it might be worth trying.

In the 9i/10g days, "alter session set events '10851 trace name context forever, level 2'" , followed by a DROP TABLE tname used to sometimes work - don't know if it still would.