This discussion is archived
3 Replies Latest reply: Jun 24, 2013 12:27 AM by ATD RSS

Recreating AQ objects

ATD Guru
Currently Being Moderated

Hi All,

 

I have been asked to recreate AQ objects that exist on one of our 11g databases on another 11g database.  The chap that created the originals is no longer available and I am struggling to work out how each of the objects is created.

 

I have to recreate the following:

 

AQ$<queuename>_TAB - VIEW

AQ$<queuename>_TAB_R - VIEW

AQ$<queuename>_TAB_S - VIEW

AQ$_<queuename>_TAB_E - QUEUE

AQ$_<queuename>_TAB_F - VIEW

AQ$_<queuename>_TAB_G - TABLE

AQ$_<queuename>_TAB_H - TABLE

AQ$_<queuename>_TAB_I - TABLE

AQ$_<queuename>_TAB_L - TABLE

AQ$_<queuename>_TAB_N - SEQUENCE

AQ$_<queuename>_TAB_S - TABLE

AQ$_<queuename>_TAB_T - TABLE

AQ$_<queuename>_TAB_V - EVALUATION CONTEXT

<queuename> - QUEUE

<queuename> - UNDEFINED

<queuename>_N - RULE SET

<queuename>_R - RULE SET

<queuename>_TAB - TABLE

 

and have so far managed to recreate:

 

AQ$<queuename>_TAB - VIEW

AQ$_<queuename>_TAB_E - QUEUE

AQ$_<queuename>_TAB_F - VIEW

AQ$_<queuename>_TAB_I - INDEX

AQ$_<queuename>_TAB_N - SEQUENCE

AQ$_<queuename>_TAB_T - INDEX

AQ$_<queuename>_TAB_V - EVALUATION CONTEXT

<queuename> - QUEUE

<queuename>_N - RULE SET

<queuename>_R - RULE SET

<queuename>_TAB - TABLE

 

I am assuming that the suffixes are determined by Oracle itself when a procedure is run, but I can not found anywhere that explains how, for example, the "_H" suffix table is created.  I have also managed to create two objects that are indexes whereas the originals are tables, yet I haven't done anything specific for indexes.  I am assuming that the DBMS package creates all of these objects but am not sure which procedure creates which suffixed object.

 

Also, is there a view in Oracle that shows the definitions of these objects (apart from the all_tables, all_indexes etc type of views)?

 

Any guidance appreciated

 

Thanks

 

Andy

  • 1. Re: Recreating AQ objects
    davidp 2 Pro
    Currently Being Moderated

    You need to create the right type of queue. Yours appears to be an Oracle 10.0 compatible multi-consumer queue. The various views and tables are described in   Oracle Streams Advanced Queuing Administrative Interface and Oracle Streams Advanced Queuing &amp;amp; Messaging Gateway Views The second reference includes descriptions of USER_QUEUE_TABLES and USER_QUEUES.

     

    The easiest ways to get the SQL to define the queue table and queue are either

    • use SQLDeveloper - connect as the queue owner or a DBA, navigate to the "Queue Tables" and the specific Queue Table, click on its name then click on the "SQL" tab. It will give you PL/SQL to recreate the queue table. Its script is over-specific - you probably don't not need the schema names, storage clause, or "Compatible". Similarly clicking on the queue in the navigation pane"Queues" item and clicking on SQL will show you PL/SQL for the queue.
    • use DBMS_METADATA:

    select dbms_metadata.get_ddl('AQ_QUEUE_TABLE', '<queue_name>_TAB')

        ,  dbms_metadata.get_ddl('AQ_QUEUE', <queue_name>)

    from dual;

    I think SQLDeveloper just calls DBMS_METADATA for you.

  • 2. Re: Recreating AQ objects
    ATD Guru
    Currently Being Moderated

    Many thanks, David

     

    I don't think that the database was 10g at any point, but it could have been set up with 10g databases in mind, perhaps.  The links seem to suggest that the main queue  and E queue objects and the I and T indexes are all that I need at this point.  The links also got me to the aqdemo files that show how to set things up and I am going to go through those.  For the tables etc that I have not already managed to recreate, I will ignore them until there looks like there is a need for them - as it is entirely possibly that these were created using 10g knowledge and may not be "live"

     

    Regards

     

    Andy

  • 3. Re: Recreating AQ objects
    ATD Guru
    Currently Being Moderated

    Update:

     

    I have just run a quick test using the aqdemo example code, and have found that setting the "multiple_consumers" parameter to TRUE in the dbms_aqadm.create_queue_table() procedure is how the _G, _H, _I, _L, _S and _T tables are created, so I'll use that to recreate the old objects

     

    Andy

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points