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.