Home » SQL & PL/SQL » SQL & PL/SQL » Reg dropping Queue tables ORA-24005: (oracle 10g)
Reg dropping Queue tables ORA-24005: [message #346269] Mon, 08 September 2008 00:55 Go to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi guys,
I tried to drop an user in my database

DROP USER DEMO CASCADE;


but it throws the following error

DROP USER DEMO 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


can any one help,reg this issue

Thanks,

[Updated on: Mon, 08 September 2008 00:59]

Report message to a moderator

Re: Reg dropping Queue tables ORA-24005: [message #346273 is a reply to message #346269] Mon, 08 September 2008 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-24005: 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.

First drop the user's queue table then drop the user.

Regards
Michel
Re: Reg dropping Queue tables ORA-24005: [message #346276 is a reply to message #346269] Mon, 08 September 2008 01:05 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => '<QUEUE_TABLE>');
DBMS_AQADM.DROP_QUEUE_TABLE
(queue_table => '<QUEUE_TABLE>', force =>TRUE);


Try dropping queue table first

Regards,
Rajat
Re: Reg dropping Queue tables ORA-24005: [message #346288 is a reply to message #346276] Mon, 08 September 2008 01:34 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
Should i execute this procedure in same user or as sys ,why because when i tried to drop in my user i got the following error

BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', 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


Thanks,
Re: Reg dropping Queue tables ORA-24005: [message #346289 is a reply to message #346288] Mon, 08 September 2008 02:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
'DEF$_AQCALL' is not a user table, it is a system one, don't drop it.

Regards
Michel
Re: Reg dropping Queue tables ORA-24005: [message #346293 is a reply to message #346289] Mon, 08 September 2008 02:20 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
hi,
How can i drop my user it shows drop queue table as an error
pls help on this

Thanks
Re: Reg dropping Queue tables ORA-24005: [message #346303 is a reply to message #346293] Mon, 08 September 2008 02:43 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
1) Identify the Queue table(s) belonging to this user. Look in User_Tables for this
2) Drop these tables using DBMS_AQADM
3) Drop the user
Re: Reg dropping Queue tables ORA-24005: [message #346317 is a reply to message #346303] Mon, 08 September 2008 03:26 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
http://www.dbasupport.com/forums/archive/index.php/t-39345.html

Check This Out.
It May Help

Regards,
Rajat
Re: Reg dropping Queue tables ORA-24005: [message #346344 is a reply to message #346317] Mon, 08 September 2008 05:21 Go to previous messageGo to next message
ram anand
Messages: 244
Registered: February 2008
Location: india
Senior Member
Hi,
I could find that the below 2 tables cannt be deleted

DEF$_AQCALL
DEF$_AQERROR

but the Owner for these queue tables is SYSTEM
now how can i drop these tables ,and will there be any problem if we delete these two tables ,so pls provide me needfull help

Thanks,
Re: Reg dropping Queue tables ORA-24005: [message #346350 is a reply to message #346344] Mon, 08 September 2008 05:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You don't want to drop those two tables - they;re owned by SYSTEM, not by the user you are trying to drop.

Unless you're trying to drop SYSTEM, in which case you should thank Great Cthulhu and al his little deep Ones that this error stopped you breaking your database beyond repair.

Thought:

When I said 'Look in USER_TABLES' I meant for you to do it while connected as the user to be dropped, not while connectes as System. I thought that would have been clear, but I may have been wrong.

Previous Topic: Timestamps and Hell.....
Next Topic: Confusion Regarding TKPROF
Goto Forum:
  


Current Time: Sat Dec 03 22:03:15 CST 2016

Total time taken to generate the page: 0.04237 seconds