# Advanced queue table script **URL:** \ **Category:** Toad for Oracle & DevOps Toolkit\ **Created:** [April 1, 2014, 1:11am UTC](https://forums.toadworld.com/t/advanced-queue-table-script/29552 "2014-04-01T01:11:02Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [April 1, 2014, 1:11am UTC](https://forums.toadworld.com/t/advanced-queue-table-script/29552/1 "2014-04-01T01:11:02Z")
Hi, When one deal with AQ tables, Toad corectly generate script, which looks like one get from dbms\_metada. BEGIN SYS.DBMS\_AQADM.CREATE\_QUEUE\_TABLE ( QUEUE\_TABLE =\> ‘MATS.DF00\_MESSAGE\_QUEUE\_TBL’ ,QUEUE\_PAYLOAD\_TYPE =\> ‘SYS.AQ$\_JMS\_MESSAGE’ ,COMPATIBLE =\> ‘10.0.0’ ,STORAGE\_CLAUSE =\> ‘NOCOMPRESS TABLESPACE MATS\_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; / And original approach (using dbms\_metadata): set serveroutput on size unlimited; set long 32000 select dbms\_metadata.get\_ddl(‘AQ\_QUEUE’,‘DF00\_MESSAGE\_QUEUE’,‘MATS’) from dual; select dbms\_metadata.get\_ddl(‘AQ\_QUEUE\_TABLE’,‘DF00\_MESSAGE\_QUEUE\_TBL’,‘MATS’) from dual; ## DBMS\_METADATA.GET\_DDL(‘AQ\_QUEUE’,‘DF00\_MESSAGE\_QUEUE’,‘MATS’) BEGIN DBMS\_AQADM.CREATE\_QUEUE( Queue\_name =\> ‘MATS.DF00\_MESSAGE\_QUEUE’, Queue\_table =\> ‘MATS.DF00\_MESSAGE\_QUEUE\_TBL’, Queue\_type =\> 0, Max\_retries =\> 5, Retry\_delay =\> 30, 1 row selected. ## DBMS\_METADATA.GET\_DDL(‘AQ\_QUEUE\_TABLE’,‘DF00\_MESSAGE\_QUEUE\_TBL’,‘MATS’) BEGIN DBMS\_AQADM.CREATE\_QUEUE\_TABLE( Queue\_table =\> ‘“MATS”.“DF00\_MESSAGE\_QUEUE\_TBL”’, Queue\_payload\_type =\> ‘SYS.AQ$\_JMS\_MESSAGE’, storage\_clause =\> ‘PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLES PACE MATS\_DATA2’, Sort\_list =\> ‘ENQ\_TIME’, Compatible =\> ‘8.1.3’); 1 row selected. But, many times (CTAS for an example) I prefer other version of description, which I could get from SQL Developer, which looks more like normal create table script. CREATE TABLE “MATS”.“DF00\_MESSAGE\_QUEUE\_TBL” ( “Q\_NAME” VARCHAR2(30 BYTE), “MSGID” RAW(16), “CORRID” VARCHAR2(128 BYTE), “PRIORITY” NUMBER, “STATE” NUMBER, “DELAY” TIMESTAMP (6), “EXPIRATION” NUMBER, “TIME\_MANAGER\_INFO” TIMESTAMP (6), “LOCAL\_ORDER\_NO” NUMBER, “CHAIN\_NO” NUMBER, “CSCN” NUMBER, “DSCN” NUMBER, “ENQ\_TIME” TIMESTAMP (6), “ENQ\_UID” VARCHAR2(30 BYTE), “ENQ\_TID” VARCHAR2(30 BYTE), “DEQ\_TIME” TIMESTAMP (6), “DEQ\_UID” VARCHAR2(30 BYTE), “DEQ\_TID” VARCHAR2(30 BYTE), “RETRY\_COUNT” NUMBER, “EXCEPTION\_QSCHEMA” VARCHAR2(30 BYTE), “EXCEPTION\_QUEUE” VARCHAR2(30 BYTE), “STEP\_NO” NUMBER, “RECIPIENT\_KEY” NUMBER, “DEQUEUE\_MSGID” RAW(16), “SENDER\_NAME” VARCHAR2(30 BYTE), “SENDER\_ADDRESS” VARCHAR2(1024 BYTE), “SENDER\_PROTOCOL” NUMBER, “USER\_DATA” “SYS”.“AQ$\_JMS\_MESSAGE” , “USER\_PROP” “SYS”.“ANYDATA” , PRIMARY KEY (“MSGID”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT) TABLESPACE “MATS\_DATA2” ENABLE ) USAGE QUEUE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT) TABLESPACE “MATS\_DATA2” LOB (“USER\_DATA”.“TEXT\_LOB”) STORE AS BASICFILE ( TABLESPACE “MATS\_DATA2” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT)) LOB (“USER\_DATA”.“BYTES\_LOB”) STORE AS BASICFILE ( TABLESPACE “MATS\_DATA2” ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT)) OPAQUE TYPE “USER\_PROP” STORE AS BASICFILE LOB ( ENABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT)) ; CREATE INDEX “MATS”.“AQ$\_DF00\_MESSAGE\_QUEUE\_TBL\_I” ON “MATS”.“DF00\_MESSAGE\_QUEUE\_TBL” (“Q\_NAME”, “STATE”, “ENQ\_TIME”, “STEP\_NO”, “CHAIN\_NO”, “LOCAL\_ORDER\_NO”) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT) TABLESPACE “MATS\_DATA2” ; CREATE INDEX “MATS”.“AQ$\_DF00\_MESSAGE\_QUEUE\_TBL\_T” ON “MATS”.“DF00\_MESSAGE\_QUEUE\_TBL” (“TIME\_MANAGER\_INFO”) PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER\_POOL DEFAULT FLASH\_CACHE DEFAULT CELL\_FLASH\_CACHE DEFAULT) TABLESPACE “MATS\_DATA2” ; Mine question is: could that be implemented in Toad as well? Brg Damir P.S. Think this is not for idea pond because this is normal functionality for AQ tables. ---
**Author:** ![damir.vadas\_250](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/damir.vadas_250/32/37_2.png) [@damir.vadas\_250](https://forums.toadworld.com/u/damir.vadas_250)\ **Post date:** [April 1, 2014, 2:45am UTC](https://forums.toadworld.com/t/advanced-queue-table-script/29552/2 "2014-04-01T02:45:42Z")
Just one addition: > Compatible =\> '8.1.3'); is known bug in dbms\_redefinition in 11.2 database ... so Toad is right! ---
**Author:** ![JohnDorlon](https://sea1.discourse-cdn.com/quest/user_avatar/forums.toadworld.com/johndorlon/32/9997_2.png) [@JohnDorlon](https://forums.toadworld.com/u/JohnDorlon)\ **Post date:** [April 2, 2014, 9:54am UTC](https://forums.toadworld.com/t/advanced-queue-table-script/29552/3 "2014-04-02T09:54:28Z")
There’s no way to get a Table script for a Queue Table in Toad, but the SB RHS tabs should give you the same information in non-script form (Columns on columns tab, etc)