Queue Rule Sets Invalid After Executing DBMS_AQADM.PURGE_QUEUE_TABLE (Doc ID 865236.1)

Last updated on MAY 05, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 to 11.2.0.2 [Release 11.1 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 03-09-2012***

Symptoms

Queue Rule sets become INVALID after purging queue tables .

Following test case demonstrate how positive and negative rule sets get
invalid on executing DBMS_AQADM.PURGE_QUEUE_TABLE.

This does not effect the basic AQ functionality.

SQL> create user test identified by test;

grant dba to test
connect test/test

User created.

Grant succeeded.

Connected.

SQL> BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE(
queue_table => 'imps_msg_dependency',queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(
queue_name => 'imps_msg_dependency_q',
queue_table => 'imps_msg_dependency'
,RETENTION_TIME => 60);
DBMS_AQADM.START_QUEUE(
queue_name => 'imps_msg_dependency_q');

END;
/

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, status from user_objects
where object_name like 'IMPS_MSG_DEPENDENCY%'
and status = 'INVALID'; 2 3

no rows selected

SQL> declare
v_purge_options_t dbms_aqadm.aq$_purge_options_t;
begin

DBMS_AQADM.PURGE_QUEUE_TABLE('IMPS_MSG_DEPENDENCY',null,v_purge_options_t);
end;

/ 2 3 4 5 6 7

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, status from user_objects
where object_name like 'IMPS_MSG_DEPENDENCY%'
and status = 'INVALID'; 2 3

OBJECT_NAME OBJECT_TYPE STATUS
-----------------------------------------------------
IMPS_MSG_DEPENDENCY_Q_N RULE SET INVALID

IMPS_MSG_DEPENDENCY_Q_R RULE SET INVALID

In 11.2, also the QUEUE might show as INVALID

Cause

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