在一套10.2.0.5.0的双节点RAC数据库上，删除用户时报出ORA-00604及ORA-24005的错误：

 
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> drop user gdhytest cascade;
drop user gdhytest 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> 

后经查找MetaLink：Ora-24005 Error Trying To Drop User Sysman Cascade [ID 456437.1] 找到原因：被删除的用户ghhytest拥有queue table。

 
SQL> set pagesize 100 
SQL> col object_name format a40 
SQL> select object_name,object_type from dba_objects
  2  where owner='GDHYTEST' AND OBJECT_NAME LIKE '%AQ%'
  3  ;
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
AQ$_GPSSTATUS_QUEUE_TABLE_H              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_I              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T              TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR              TABLE
SQL>

解决方法：

1 gdhytest登录数据库，执行DBMS_AQADM.DROP_QUEUE_TABLE进行删除queue talbe:

 
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2 发现权限不够，赋权，重新删除：

 
SQL> conn / as sysdba;
Connected.
SQL> grant dba to gdhytest;
Grant succeeded.
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24
characters
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE GDHYTEST.GPSSTATUS_QUEUE_TABLE_H does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1

3 依然报错！！！QUEUE_TABLE too long,不得已，重命名queue table进行删除：

 
SQL> rename  AQ$_GPSSTATUS_QUEUE_TABLE_H to queue1;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
QUEUE1                         TABLE
7 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE1',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
6 rows selected.
SQL> 

4 如法炮制，重命名其它queue table,然后执行删除：

 
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_I to queue_a;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_NR to queue_b;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_T to queue_c;
Table renamed.
SQL> rename aq$_GPS_TEMP_QUEUE_TABLE_NR to queue_d;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_60452             TABLE
SYS_IOT_OVER_60459             TABLE
QUEUE_B                        TABLE
QUEUE_A                        TABLE
QUEUE_C                        TABLE
QUEUE_D                        TABLE
6 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_A',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_B',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_C',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_D',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
no rows selected

5 最后彻底删除gdhytest用户：

 
SQL> conn / as sysdba;
Connected.
SQL> drop user gdhytest cascade;
User dropped.
SQL>