v9.0 Queue Table Views get created and compared wrongly

[Bernhard S]

When you create a Queue Table it automatically creates AQ$_ views with it, i.e. when you use:

begin
  sys.dbms_aqadm.create_queue_table(
     queue_table        => 'MY_QUEUE_TABLE'
    ,queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE');
end;
/

the following objecst will be created with it:

SQL> select object_name, object_type from user_objects where object_name like '%MY_QUEUE_TABLE%';

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
AQ$MY_QUEUE_TABLE         VIEW
AQ$_MY_QUEUE_TABLE_E      QUEUE
AQ$_MY_QUEUE_TABLE_F      VIEW
AQ$_MY_QUEUE_TABLE_I      INDEX
AQ$_MY_QUEUE_TABLE_T      INDEX
MY_QUEUE_TABLE            TABLE

6 rows selected

SQL>

Currently PSD doesn't deal with this fact properly. When you do a "Export User Objects" you find the sys.dbms_aqadm.create_queue_table() procedure call but in addition als the create scripts for the AQ$ views. The latter should not be there. It is in fact wrong creating the views this way. The View AQ$__F ends with:

WHERE qt.q_name = qo.name AND qo.owner =   '<source_schema_name>' WITH READ ONLY;

If your target_schema_name is different from source_schema_name this view is created wrongly.
This should also be taken into account when comparing two schemas with both having AQ$__F Views. In this WHERE-clause they will always be different for different schemas, which is not a difference, but required!

 

[Bernhard S]

When you look at the "Object Browser" - "Queue Tables", pick a Queue table there and go into "Referenced By" folder there, PSD shows a turquois question mark there as the icon for Queues, while in "Object Browser" - "Queues" it shows the correct Queue icon.