8 Replies Latest reply: Jan 6, 2012 8:50 AM by 909018 RSS

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

    909018
      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!
        • 1. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
          damorgan
          Is this a dev/test environment or production with important data?

          If the later close this forum thread and open an SR with Oracle Support immediately.

          You can potentially do a lot more damage ... and we don't even know your version number ... not to mention you should never have executed that ALTER SESSION with being instructed to do so by Oracle.
          • 2. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
            909018
            its a test environment!
            and yes, I know I couldnt do that, but unfortntly I did and I need to fix it :(
            Just now I saw the problem that I created...

            somebody knows how to fix it ?

            [AND THE ORACLE VERSION IS 10.0.1.4.0]
            • 3. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
              jhoward
              Just curious, does this return anything?
              SELECT *
                FROM recyclebin
               WHERE original_name = 'QUEUE_INIT_I1';
              • 4. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
                909018
                I'll try that tomorrow !!
                now, Im not at work anymore :(
                but it's gonna be the first thing I'll do in the morning!
                And then, come back here to tell you

                thanks!!
                • 5. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
                  758358
                  Hi,

                  Firstly - as someone has already mentioned, never use events that haven't been recommended by support or are well documented for use (e.g. 10046 for sql trace), for AQ always use the given API's (DBMS_AQ, DBMS_AQADM), you should not interact directly with Queue objects unless it is documented in either the standard Oracle documentation or a support note or you have been instructed to do so by a support engineer from Oracle.

                  To resolve your current issue I suspect you should be able to work through (some bits may already be complete due to your manual table drops) the process in the following MOS note:

                  How to Manually Cleanup Advanced Queuing Tables (Doc ID 203225.1)

                  Thanks,
                  Paul
                  • 6. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
                    909018
                    Yeah, I know now that I couldn't do that... I learned my lesson :(

                    JHOWARD,
                    I did the query you asked me to do and it returned no rows ... So it's not in the recycle bin [when you said about that, I really tought that it should be there but its not :( damn]


                    PDTILL2508 (PAUL),
                    I couldn't find these notes ... Where can I find it?
                    I was looking at the Oracle Database Documentation Library, but nothing ...

                    Thanks for all the help!!
                    • 7. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
                      758358
                      >
                      PDTILL2508 (PAUL),
                      I couldn't find these notes ... Where can I find it?
                      I was looking at the Oracle Database Documentation Library, but nothing ...
                      It is a My Oracle Support (Metalink as was) note, go to support.oracle.com

                      Thanks
                      Paul
                      • 8. Re: Ghost QUEUE : exists to DBA but doesnt to the right schema
                        909018
                        It doesnt exists anymore at metalink.oracle.com

                        but its ok ... I fixed the problem!
                        looking the query that DBA_QUEUES and DBA_QUEUE_TABLES does, I found the following tables:

                        aq$_queues
                        aq$_queue_tables

                        the DBA_QUEUES does a join between those tables, and the problem was that the PK was different in both. So, it couldnt show the queues in DBA_QUEUES because of that ...

                        but, as I said, the queue didn't exists, and I just wanted to clean it from the system ... to be able to drop the schema!

                        so, I doing this query:


                        SELECT OWNER, OBJECT_NAME FROM DBA_OBJECTS WHERE UPPER(OBJECT_NAME) LIKE 'AQ%' AND OBJECT_TYPE='TABLE' AND OWNER IN ('SYS','SYSTEM','OWNER_OF_QUEUE');


                        I found the candidates tables that could have registers about this queue ...
                        then I did a search in every table looking for a table_name [or some column like that] that could have a register about that queue ...
                        So I deleted those registers from there, and from obj$
                        Finally, all the register about that queue was gone, and I was able to drop the schema

                        I know that what I did was risk (TOO TOO risk), but was the only way to be able to drop the schema ...
                        but dropping the schema, my problem was over and I was able to recreate it without problems...

                        Not the best solution, but ... what can I do?

                        but thanks for everyone that helped me !! I really appreciated that!