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

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.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google