SQL> drop tablespace EXAMPLE INCLUDING CONTENTS;
drop tablespace EXAMPLE INCLUDING CONTENTS
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

EXAMPLE表空间是Oracle的示例表空间，通常保存着诸如OE, SH, IX, HR等示例用户的表数据，在尝试删除这个表空间的时候遇到上述的错误。

实际上错误是由于IX用户下的高级队列表对象引起的，高级队列表对象无法使用常规的drop命令删除，无论是删除包含AQ的表空间还是用户，都会报ORA-24005错误。

SQL> drop user IX cascade;
drop user IX cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

SQL> select table_name,tablespace_name  from dba_tables where owner='IX';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
SYS_IOT_OVER_53828             EXAMPLE
AQ_ORDERS_QUEUETABLE_S        EXAMPLE
SYS_IOT_OVER_53842             EXAMPLE
AQ_STREAMS_QUEUE_TABLE_S      EXAMPLE
ORDERS_QUEUETABLE              EXAMPLE
STREAMS_QUEUE_TABLE            EXAMPLE
AQ_STREAMS_QUEUE_TABLE_T
AQ_STREAMS_QUEUE_TABLE_I
AQ_STREAMS_QUEUE_TABLE_H
AQ_STREAMS_QUEUE_TABLE_G
AQ_STREAMS_QUEUE_TABLE_C
AQ_ORDERS_QUEUETABLE_T
AQ_ORDERS_QUEUETABLE_I
AQ_ORDERS_QUEUETABLE_H
AQ$_ORDERS_QUEUETABLE_G

常规的方法是按照提示，使用DBMS_AQADM.DROP_QUEUE_TABLE存储过程来完成。

SQL> conn ix/ix
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('ORDERS_QUEUETABLE',true);
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE('STREAMS_QUEUE_TABLE',true);

在某些时候运行完上述存储过程，用户下仍然还会存在一些AQ$表，此时仍然无法正常删除用户或者表空间。

SQL> select table_name,tablespace_name  from dba_tables where owner='IX';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AQ$_STREAMS_QUEUE_TABLE_C

此时需要通过10851 event来进行手工删除，操作顺序如下，更加详细的步骤可以参看Metalink Note 203225.1。

SQL> conn ix/ix
SQL> SELECT object_name, object_type FROM user_objects WHERE object_name like '%STREAMS_QUEUE_TABLE%';

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
AQ_STREAMS_QUEUE_TABLE_C                TABLE
AQ_STREAMS_QUEUE_TABLE_Y                INDEX

SQL> ALTER SESSION SET EVENTS '10851 trace name context forever, level 2';

Session altered

SQL> DROP TABLE AQ$_STREAMS_QUEUE_TABLE_C cascade constraints;

Table dropped

SQL> SELECT object_name, object_type FROM user_objects WHERE object_name like '%STREAMS_QUEUE_TABLE%';

OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------

SQL> drop user ix cascade;

User dropped

SQL> drop tablespace example;

Tablespace dropped