Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Ghost QUEUE : exists to DBA but doesnt to the right schema

909018Jan 4 2012 — edited Jan 6 2012
Hello everyone!
Im having some problems with Advanced queue ...

I had to drop the queues from my schema, and unfortntly I made a bad decision to do that (I guess)
To drop the Queue table, I executed the following command:

alter session set events '10851 trace name context forever, level 2';

then, I dropped like a usual table:

DROP TABLE QUEUE_INIT_I1

Some problems came with this ... I tried to drop the queue, but I couldn't (dont remember why, some kind of problem about the queue_table that I dropped). So, looking at the internet I found the following command:

alter session set events '25475 trace name context forever, level 2';

[Just like I did with the queue_table]

then, I dropped it like a normal queue:

exec exec DBMS_AQADM.DROP_QUEUE('QUEUE_INIT_I1');


So ... here comes the problem: I tried to drop the user, then I couldn't, because I get the following error:

ORA-24008: queue table USER.QUEUE_INIT_I1 must be dropped first

But it already been dropped!
With SYSTEM, I did the query:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS where object_name LIKE 'QUEUE_INIT_I1'

OWNER OBJECT_NAME OBJECT_TYPE STATUS

USER QUEUE_INIT_I1 QUEUE INVALID

But with the user USER, I cant see the object! So, it's kind of a ghost queue. DBA_OBJECTS says that it exists, but in the right schema, it doesn't show up!

Then, I tried to drop it, but the DBMS_AQADM says that it doesn't exists... I tried to recreate the following queue and DBMS_AQADM says that IT DOES EXISTS!
It's a paradox


So, anybody knows why this is happening? and how can I fixed? (to drop the user, or just recreate the queue again)


Thanks for helping!
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 3 2012
Added on Jan 4 2012
8 comments
1,507 views