If you have accidentally or purposefully deleted the message rows from an AQ table when its subscriber was ON, i.e. the consumer was dequeuing - Then you might face this issue once the dequeue thread tries to dequeue - ORA-00600 exception.
For rectification, perform following:
Stop the Consumer.
connect / as sysdba
show parameter aq
-- take note of the initial setting =<original_value>
alter system set aq_tm_processes=0 scope=memory;
connect <USER>/password
delete from AQ$_<QUEUE_TABLE>_I i
where not exists (select t.msgid from <QUEUE_TABLE> t where i.msgid = t.msgid)
/
delete from AQ$_<QUEUE_TABLE>_H h
where not exists (select t.msgid from <QUEUE_TABLE> t where h.msgid = t.msgid)
/
delete from AQ$_<QUEUE_TABLE>_T ti
where not exists (select t.msgid from <QUEUE_TABLE> t where ti.msgid = t.msgid)
/
commit;
connect / as sysdba
alter system set aq_tm_processes=<original_value> scope=memory;
Things should be working now.
