How To Set Exception Queue Table To Reside in a Different Tablespace than the Base Queue Table (Doc ID 1338832.1)

Last updated on JULY 29, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.2 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Goal


Having the following CREATE_QUEUE_TABLE statement:

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE(
Queue_table => '<queue_table_name>',
Queue_payload_type => 'SYS.AQ$_JMS_BYTES_MESSAGE',
storage_clause => 'TABLESPACE USERS PCTFREE 40 PCTUSED 10 INITRANS 10 STORAGE(INITIAL 10485760 NEXT 10485760 MINEXTENTS 1 MAXEXTENTS unlimited) LOB (USER_DATA.BYTES_LOB) STORE AS BASICFILE (TABLESPACE TOOLS DISABLE STORAGE IN ROW CHUNK 32768 STORAGE(INITIAL 52428800 NEXT 52428800 MINEXTENTS 1 MAXEXTENTS unlimited))',
Sort_list => 'PRIORITY,ENQ_TIME',
Compatible => '8.1.3',
comment => '<comment>');
END;
/

Is there a way to alter the storage attributes for the Exception queue only so that _e (exception IOT of the queue table) is in another tablespace than the queue_table ?


Solution

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms