SQL> drop user admin cascade;
drop user admin cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24170:
ORA-06512: at "SYS.DBMS_RULE_ADM", line 129
ORA-06512: at line 1

SQL> ALTER SESSION SET EVENTS = '25475 TRACE NAME CONTEXT FOREVER, LEVEL 2';

Session altered.

SQL> select owner,queue_table from dba_queue_tables where owner='ADMIN';

no rows selected

SQL> alter session set events '10046 trace name context forever ,level 12' ;

Session altered.

SQL> drop user admin cascade;
drop user admin 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> alter session set events '10046 trace name context off' ;

Session altered.

看trace文件
cd /home/oracle/app/oracle/admin/zydeve/udump
[root@zydeve udump]# ls -l|head
total 376
-rw-r----- 1 oracle oinstall 626 Aug 21 09:55 zydeve_ora_15698.trc
-rw-r----- 1 oracle oinstall 86400 Aug 21 10:09 zydeve_ora_15725.trc
-rw-r----- 1 oracle oinstall 626 Jul 1 08:35 zydeve_ora_20032.trc
-rw-r----- 1 oracle oinstall 1794 Jul 1 08:36 zydeve_ora_20210.trc
-rw-r----- 1 oracle oinstall 562 Jun 23 13:15 zydeve_ora_20243.trc
-rw-r----- 1 oracle oinstall 562 Jun 23 13:18 zydeve_ora_20265.trc
-rw-r----- 1 oracle oinstall 996 Jun 23 13:41 zydeve_ora_20404.trc
-rw-r----- 1 oracle oinstall 211237 Jun 23 13:50 zydeve_ora_20439.trc
-rw-r----- 1 oracle oinstall 15856 Jun 23 13:55 zydeve_ora_20450.trc

找到对应时间点的trace文件
[root@zydeve udump]# grep err zydeve_ora_15725.trc|more

SQL> select object_type,object_name from dba_objects where object_name like '%S_MT%' and owner='ADMIN';

OBJECT_TYPE
------------------
OBJECT_NAME
--------------------------------------------------------------------------------
TABLE
AQ$_SS_MT_TAB_G

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> conn /as sysdba
Connected.
SQL> ALTER SESSION SET EVENTS '10851 trace name context forever, level 2';
drop table admin.AQ$_SS_MT_TAB_G cascade constraints;
Session altered.

SQL>

Table dropped.

SQL> drop user admin cascade;

User dropped.

补充：如果有RULE_SET 就用EXEC DBMS_RULE_ADM.DROP_RULE_SET('SMS_MT_QUEUE_N');

ITPub博客

因为队列表而无法删除用户(1)

最新文章