Bug in object description for engueue tables

Toad 21.1.0.22, DB client 11.2.0.4, Win7 Sp1 x64, DB server 11.2.0.3

I get two different results on same object (queue table definition):

select dbms_metadata.get_ddl(object_type => ‘AQ_QUEUE_TABLE’, name => ‘DF00_MESSAGE_QUEUE_TBL’, schema => ‘USER’) as xxx from dual;
BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => ‘“USER”.“DF00_MESSAGE_QUEUE_TBL”’,
Queue_payload_type => ‘SYS.AQ$_JMS_MESSAGE’,
storage_clause => ‘PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USER_DATA2’,
Sort_list => ‘ENQ_TIME’,
Compatible => ‘8.1.3’);
END;

On DF00_MESSAGE_QUEUE_TBL when I press “F4” I get:
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => ‘USER.DF00_MESSAGE_QUEUE_TBL’
,QUEUE_PAYLOAD_TYPE => ‘SYS.AQ$_JMS_MESSAGE’
,COMPATIBLE => ‘10.0.0
,STORAGE_CLAUSE => ‘NOCOMPRESS
TABLESPACE USER_DATA2
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)’
,SORT_LIST => ‘ENQ_TIME’
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
,SECURE => FALSE
);
End;
/

Problem is compatible version which is somehow wrong when run from describe object (F4). From sqlplus I get result as it is firstly shown-8.1.3

I’m pretty sure that you can reproduce that bug.

I can’t reproduce it. When I create the table, the compatibility shows as 8.1.3 in user_queue_tables and in the SB. The script tab produces ‘8.1’ as the compatibility level, which I guess is a little off, but I’m not seeing 10.0 as you are.

What do you see in dba_queue_tables as the compatibility?

SQL> select compatible from dba_queue_tables where queue_table = ‘DF00_MESSAGE_QUEUE_TBL’;

COMPAT

10.0.0

SQL>

Uf … I think we were chasing rabbit and catch a fox.

:slight_smile:

It’s interesting that dbms_metadata comes up with 8.1.3 even though 10.0.0 is in dba_queue_tables. Can you determine where 8.1.3 is stored in the data dictionary?

Can you determine where 8.1.3 is stored in the data dictionary?

Could you please specify ... what exactly you mean.

I am wondering how dbms_metadata comes up with 8.1.3 for the compatible level of that queue table if dba_queue_tables shows 10.0.0. I am thinking that there
is some other table or view in the SYS schema where it finds that information.

From: damir.vadas_531 [mailto:bounce-damirvadas_531@toadworld.com]

Sent: Thursday, February 27, 2014 10:38 AM

To: toadoracle@toadworld.com

Subject: RE: [Toad for Oracle - Discussion Forum] Bug in object description for engueue tables

RE: Bug in object description for engueue tables

Reply by damir.vadas_531

Can you determine where 8.1.3 is stored in the data dictionary?

Could you please specify ... what exactly you mean.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - General
notifications altogether.

Toad for Oracle - Discussion Forum

Flag
this post as spam/abuse.

nevermind, I see the same thing with dbms_metadata…I’ll find it.

Good that you can reproduce that too.

Seems that this is not Toad but Oracle problem because something seems to be not synchronized.

Please let me know your findings…

Oops, I spoke to soon. I get an 8.1.3 from dbms_metadata, but that is the same as what I have in dba_queue_tables!

Was your database upgraded? I think you have a wrong/old dba_queue_tables view. DBMS_METADATA gets its information from SYS.KU$_QUEUE_TABLE_VIEW, but the data is somewhat cryptic there.

John

As I said, seems that we found a fox instead of rabbit. About db history I do not know to much, but I’m pretty sure that starting point was higher then 10.1 and certainly not lower.

Now I’m home but tomorrow will open SR to see what they say.

For me this case is close as far as Toad involvement in this case.

Brg

Damir

Bug 14577029 : DBMS_METADATA.GET_DDL DISPLAYS INCORRECT VALUE FOR QUEUE TABLE COMPATIBILITY

This bug is fixed already within RDBMS patch-set 11.2.0.4: