My Oracle Support Banner

DBMS_METADATA.GET_DDL Outputs Different STORAGE Parameters For AQ_QUEUE_TABLE Type Between RDBMS 11g and 12c and up. (Doc ID 2723161.1)

Last updated on FEBRUARY 03, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Symptoms

Whenever comparing the output from DBMS_METADATA.GET_DDL between 11g and 12c or up, specifically for AQ_QUEUE_TABLE Type object, the STORAGE part will return differently among these versions.

As for example, we can demonstrate with below code sample:

sqlplus / as sysdba

grant dba to TEST identified by testcase;

conn TEST/testcase

CREATE type testtype1 as object (n number);
/

begin
DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => 'TEST1', queue_payload_type => 'testtype1');
end;
/

set line 200
set long 2000000000

select dbms_metadata.get_ddl('AQ_QUEUE_TABLE', 'TEST1', 'TEST') from dual;

-- Results:

-- 11g and below:

BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '"TEST"."TEST1"',
Queue_payload_type => 'TEST.TESTTYPE1',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE USERS',
Sort_list => 'ENQ_TIME',
Compatible => '10.0.0');
END;

-- 12c and above:

BEGIN DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '"TEST"."TEST1"',
Queue_payload_type => '"TEST"."TESTTYPE1"',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 8 NEXT 1 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE USERS',
Sort_list => 'ENQ_TIME',
Compatible => '10.0.0');
END;

 

Changes

 Retrieving DDL metadata from an Advanced Queue Table object.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.