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

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 => '<queue table>',queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',multiple_consumers => TRUE);
DBMS_AQADM.CREATE_QUEUE(
queue_name => '<queue name>',
queue_table => '<queue table>'
,RETENTION_TIME => 60);
DBMS_AQADM.START_QUEUE(
queue_name => '<queue name>');

END;
/

PL/SQL procedure successfully completed.

SQL> select object_name, object_type, status from user_objects
where object_name like '<queue table>%'
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('<queue table>',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 '%<queue table>%'
and status = 'INVALID'; 2 3

OBJECT_NAME OBJECT_TYPE STATUS
-----------------------------------------------------
<queue table>_Q_N RULE SET INVALID

<queue table>_Q_R RULE SET INVALID

In 11.2, also the QUEUE might show as INVALID

Cause

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