Warm tip: This article is reproduced from serverfault.com, please click
oracle plsql ora-00942

Cannot drop Oracle queue table with DBMS_AQADM.DROP_QUEUE_TABLE

发布于 2010-05-12 14:48:41

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_LOG that needs to go away. I have successfully stopped and dropped the associated queue:

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?

Questioner
nw.
Viewed
0
分享
dpbradley 2010-05-13 00:05:40

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.

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.

In the 9i/10g days, "alter session set events '10851 trace name context forever, level 2'" , followed by a DROP TABLE tname used to sometimes work - don't know if it still would.

nw. 2010-05-12 16:34:21

Yes, I had several failed attempts to drop the queue table by issuing dbms_aqadm calls out of order. Your alter session suggestion worked for me on 11g. Very helpful, thanks.

Merlyn Morgan-Graham 2010-10-09 03:05:00

Ugh. I reinstalled XE (10.2) to solve this on my end :p I ended up w/ this problem while trying to DROP USER <username> CASCADE. Is there a better way to do this, if my goal is simply to drop a whole user/schema?

dpbradley 2010-10-09 20:11:12

@Merlyn - not that I know of. If you want to routinely DROP USER with this command to rebuild, one solution is to separate the queues into a separate schema and make the appropriate enqueue/dequeue grants to the schema that will be dropped and recreated.

Merlyn Morgan-Graham 2010-10-10 05:14:10

That sounds like a good suggestion, though I think I won't be able to use it. It is hard to check in a change to the schema in the project I'm working on. Fortunately I don't intend to drop the user often at all (I only did it because I messed up while building a local dev instance of my DB). It still seems like there should be some way to force wipe something like this, though I guess most real users would simply keep backups. Thanks for the help :)

热门帖子

1
C++新手，求助一个关于怎么使用第三方库的问题
2
关于英语学习的重要性的思考
3
出美版 iPhone 13promax， V 友 4600 吧
4
这里分享一个免费的在线 PDF 总结工具： NoteGPT
5
没想到 Arc 浏览器对网络要求如此严格
6
深陷消费主义陷阱的背后，是我空洞的灵魂
7
做了一个以凡人修仙传境界为基础的 Github 统计卡片
8
从程序员角度思考一下，为什么我的支付宝能收到其他人的 12306 购票推送消息
9
[上海] 招中级前端开发工程师
10
澳大利亚🇦🇺归来~第一次去南半球，虽然看过很多次照片，亲临大洋路时仍觉震撼

热门github

1
Implementation of paper - YOLOv9: Learning What You Want to Learn Using Programmable Gradient Information
2
A Windows and Office activator using HWID / Ohook / KMS38 / Online KMS activation methods, with a focus on open-source code and fewer antivirus detections.
3
Get up and running with Llama 2, Mistral, Gemma, and other large language models.
4
该项目可以让你通过订阅的方式使用Cloudflare WARP+，自动获取流量。This project enables you to use Cloudflare WARP+ through subscription, automatically acquiring traffic.
5
Multi functional app to find duplicates, empty folders, similar images etc.
6
Xray panel supporting multi-protocol multi-user expire day & traffic & ip limit (Vmess & Vless & Trojan & ShadowSocks & Wireguard)
7
The Free Software Media System
8
lightweight, standalone C++ inference engine for Google's Gemma models.
9
📚 Freely available programming books
10
A collective list of free APIs
11
1️⃣🐝🏎️ The One Billion Row Challenge -- A fun exploration of how quickly 1B rows from a text file can be aggregated with Java
12
🎓 Path to a free self-taught education in Computer Science!
13
Curso para aprender el lenguaje de programación Python desde cero y para principiantes. 75 clases, 37 horas en vídeo, código, proyectos y grupo de chat. Fundamentos, frontend, backend, testing, IA...
14
This repository contains System Design resources which are useful while preparing for interviews and learning Distributed Systems
15
Mamba is a new state space model architecture showing promising performance on information-dense data such as language modeling, where previous subquadratic models fall short of Transformers. It is based on the line of progress on structured state space models, with an efficient hardware-aware design and implementation in the spirit of FlashAttention.