Friday, October 23, 2009

ORA-24062 ( Subscriber table inconsistent with queue table)

Surprisingly little search results in either google, the oracle fora, or metalink for this error. Ran into this today, and could drop an recreate the queues:
SQL> BEGIN
2 SYS.DBMS_AQADM.STOP_QUEUE(QUEUE_NAME =>'TAB_OWNER.TB_BTR_Q_OPDR');
3 SYS.DBMS_AQADM.DROP_QUEUE(QUEUE_NAME =>'TAB_OWNER.TB_BTR_Q_OPDR');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-24062: Subscriber table TAB_OWNER.AQ$_TB_BTR_Q_TBL_S inconsistent with
queue table TAB_OWNER.TB_BTR_Q_TBL
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4932
ORA-06512: at "SYS.DBMS_AQADM", line 240
ORA-06512: at line 2

The solution is drastic: for each error, reporting the table, force a drop of that table:
SQL> exec SYS.DBMS_AQADM.DROP_QUEUE_TABLE (
QUEUE_table => 'TAB_OWNER.TB_BTR_Q_TBL',
force=>TRUE);

PL/SQL procedure successfully completed.

After that, recreate the queues and queue tables.