无法使用 DBMS_AQADM.DROP_QUEUE_TABLE 删除 Oracle 队列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2819906/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot drop Oracle queue table with DBMS_AQADM.DROP_QUEUE_TABLE
提问by nw.
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_LOGthat needs to go away. I have successfully stopped and dropped the associated queue:
我正在尝试将意外安装的 LOG4PLSQL 清理到错误的（即 SYS）模式中。有一个叫队列表QTAB_LOG需要离开。我已经成功停止并删除了相关的队列：
call DBMS_AQADM.STOP_QUEUE('LOG_QUEUE');
call DBMS_AQADM.DROP_QUEUE('LOG_QUEUE');
But dropping 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?
我究竟做错了什么？
回答by dpbradley
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.
您之前是否曾尝试删除失败的队列表？这种孤队列表的情况，通常是由于使用AQ API调用时出现异常导致的问题。
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.
我不知道这是什么时候引入的，但至少 11g 现在有一个用于 drop_queue_table 调用的 FORCE 参数，它作为删除表过程的一部分停止和删除队列。在您的情况下，它可能为时已晚，但可能值得一试。
In the 9i/10g days, "alter session set events '10851 trace name context forever, level 2'" , followed by a DROP TABLE tnameused to sometimeswork - don't know if it still would.
在9I / 10G天，“ALTER SESSION事件集‘永远的10851名迹背景下，2级’”，随后DROP TABLE TNAME用来有时工作-不知道，如果它仍然会。
回答by Pankaj
I use oracle 11g r2. Below works fine for me. Not sure if all the versions support below or not.
我使用oracle 11g r2。下面对我来说很好用。不确定是否所有版本都支持以下。
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:
如果您想自己完成所有步骤（手动完成所有步骤），请按以下顺序进行：
- Stop the associated queue.
- Drop the associated queue.
- 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.
我假设您将有权执行 dbms_aqadm pkg 函数，否则调用这些 pkg 将导致错误。我希望这是有道理的。
回答by Velan
Alter session set events '10851 trace name context forever, level 2
永远更改会话设置事件 '10851 跟踪名称上下文，级别 2
回答by sudheergodgeri
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' .
给定的 Soln 对 10.2.0.3 工作正常——我们能够删除在尝试删除的模式所有者的 user_tables 中列出的队列表：在使用上面的 'alter session set events '10851 trace name context ever' 之后它工作正常。
