Cannot drop Oracle queue table with DBMS_AQADM.DROP_QUEUE_TABLE

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 dro开发者_JAVA百科pping 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?

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.

I use oracle 11g r2. Below works fine for me. Not sure if all the versions support below or not.

EXEC dbms_aqadm.drop_queue_table ( queue_table => '<OWNER>.<QUEUETABLE>',force=>true);

Above command automatically stops and drops the associated queues and then drops the queue table.

If you want to do all the steps your self(everything manually) then do it in below sequence:

1. Stop the associated queue.
2. Drop the associated queue.
3. Drop the queue table.

I am assuming that you will be having rights for executing dbms_aqadm pkg functions else it calling these pkgs will result in error. I hope this makes sense.

Alter session set events '10851 trace name context forever, level 2

Soln given works fine for 10.2.0.3 -- we were able to drop the queue table listed in user_tables of schema owner where the drop was attempted: it worked ok after using above 'alter session set events '10851 trace name context forever' .