Ghost QUEUE : exists to DBA but doesnt to the right schema
909018Jan 4 2012 — edited Jan 6 2012Hello 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!