1 2 3 Previous Next 34 Replies Latest reply: May 21, 2014 4:09 AM by Martin Preiss RSS

    Create Queue

      Hi All,

       

      11.2.0.3

       

      I am creating a queue and queue table using :

       

         BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(

           Queue_table        => '"MCPGOWNER"."MERCHANT_NOTIFY_QT"',

           Queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',

           storage_clause     => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USERS',

           Sort_list          => 'ENQ_TIME',

           Compatible         => '8.1.3');

        END;

      /

         BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(

      *

      ERROR at line 1:

      ORA-24001: cannot create QUEUE_TABLE, MCPGOWNER.MERCHANT_NOTIFY_QT already

      exists

      ORA-06512: at "SYS.DBMS_AQADM", line 81

      ORA-06512: at line 1

       

      But the If I check the schema it does not exist

       

      Please help...

       

      Thanks,

      pK

        • 1. Re: Create Queue
          Martin Preiss

          and does it work if you specify a different queue table?

          • 2. Re: Create Queue
            Thierry H.

            How did you 'check the schema'?

             

            Please post result of

            select *
            from dba_queue_tables
            where queue_table = 'MERCHANT_NOTIFY_QT';
            

             

            HTH,

            Thierry

            • 3. Re: Create Queue

              select *

              from dba_queue_tables

              where queue_table = 'MERCHANT_NOTIFY_QT';   

               

               

               

              no rows selected

               

              It gets created if different name But our migration app need this name.

              • 4. Re: Create Queue
                Thierry H.

                Well... then drop the queue and (re)create it, right?

                • 5. Re: Create Queue
                  Martin Preiss

                  maybe an object of different type in the same namespace?

                  select * from dba_objects where object_name = 'MERCHANT_NOTIFY_QT'; 

                   

                  • 6. Re: Create Queue
                    Thierry H.

                    It is then not a queue table.... maybe a different queue type?

                     

                    Can you post result of:

                    select *
                    from dba_queues
                    where name = 'MERCHANT_NOTIFY_QT'
                    or queue_table = 'MERCHANT_NOTIFY_QT'
                    

                     

                    Regards,

                    Thierry

                    • 7. Re: Create Queue

                      Thanks all,

                       

                      SQL> select * from dba_queues

                      where name = 'MERCHANT_NOTIFY_QT'

                      or queue_table = 'MERCHANT_NOTIFY_QT' ;

                       

                       

                      no rows selected

                       

                      SQL>  select * from dba_objects where object_name = 'MERCHANT_NOTIFY_QT';

                       

                      MCPGOWNERMERCHANT_NOTIFY_QT9032890328TABLE

                       

                       

                      What kind of table is this? Is queue table considered a table?

                      • 8. Re: Create Queue
                        John Stegeman

                        You've just answered your own question, haven't you?

                        • 9. Re: Create Queue

                          I am migrating our prod to another  higher version so I am using expdp.

                           

                          I check the prod by using sqldev tree, and I can see that MERCHANT_NOTIFY_QT exist on both  QUEUE TABLE,  and  TABLE  tree.

                          Why is that in my target database does not exist on QUEUE TABLE tree?  But only exist the TABLE tree? Weird

                           

                          Please help....

                          • 10. Re: Create Queue
                            Hemant K Chitale

                            Have you imported (impdp) into the target database ?

                            Did you check if the import raised errors trying to import the Queue Table ?

                             

                             

                            Hemant K Chitale


                            • 11. Re: Create Queue
                              Thierry H.

                              Did you crosscheck that those queue objects actually got successfully exported? (check log file)

                               

                              Did you crosscheck that those queue objects actually got successfully imported? (check log file)

                               

                              Refer to Managing Oracle Streams Advanced Queuing

                               

                              HTH,

                              Thierry

                              • 12. Re: Create Queue

                                I think the SYS user did the creation, so it was not exported at schema level? Or maybe it did just have error. That is why I am creating it manually based on the target source object.

                                But I can not

                                • 13. Re: Create Queue
                                  Thierry H.
                                  I think the SYS user did the creation...

                                  Proves that you did not check the export / import logfiles since the user is clearly specified... for example

                                  ;;;

                                  Export: Release 11.2.0.3.0 - Production on Tue May 20 06:11:57 2014

                                  Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

                                  ;;;

                                  Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

                                  Starting "SYSTEM"."SYS_EXPORT_FULL_01":  system/********@sid parfile=/.../..../fullsid.par

                                  Estimate in progress using BLOCKS method...

                                  Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA

                                  This as well (and unfortunately) shows that you did not check and therefore cannot confirm that your queue objects were successfully exported and imported.

                                   

                                  Thierry

                                  • 14. Re: Create Queue

                                    Well, I guess I read some docs that queue usually can not be exported/imported hence you have to recreate. I will try to drop the tables and get back to you whats the result

                                    1 2 3 Previous Next