删除用户报ORA-24005错误及解决办法
在一套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>
From OracleOnLinux, post 删除用户报ORA-24005错误及解决办法
Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.
good job ， study