Validate Exception Queue: ORA-24017: cannot enable enqueue on QUEUE

advertisements

_____________________________________________________________________________________________________________________

Error Description:

exec DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E'); command is failing with following error.

SQL>  exec DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E');

BEGIN DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E'); END;

*

ERROR at line 1:

ORA-24017: cannot enable enqueue on QUEUE, JMSUSER.AQ$_TEST_TABQ_E is an exception queue

ORA-06512: at "SYS.DBMS_AQADM", line 270

ORA-06512: at line 1

Solution Description:

By default DBMS_AQADM.START_QUEUE command tries to enable both enqueue and dequeue operations. But in case of exceptional queues enqueue operation is not supported. You have to specify only the dequeue operation needs to be enabled with DBMS_AQADM.START_QUEUE command.

SQL> select name, queue_table, queue_type from user_queues where name ='AQ$_TEST_TABQ_E';

NAME                  QUEUE_TABLE                 QUEUE_TYPE

--------------------- --------------------------- ------------

AQ$_TEST_TABQ_E       TEST_TABQ                   EXCEPTION_QUEUE

Command to validate the queue is

exec DBMS_AQADM.START_QUEUE(QUEUE_NAME=>'AQ$_TEST_TABQ_E',ENQUEUE=>FALSE,DEQUEUE=>TRUE);

PL/SQL procedure successfully completed.

Or

exec DBMS_AQADM.START_QUEUE('AQ$_TEST_TABQ_E',FALSE,TRUE);

PL/SQL procedure successfully completed.

_____________________________________________________________________________________________________________________

Website Stats