Thanks in advance for helping.
We have one queue table. The data volume in this queue table very very low.
But someone tried to do a manual delete from the queue table. Now I think the queue table has been corrupted.
Some of the jobs which reads from the queue table is not able to read it, its constantly trying to read and not finding any message. Whereas some other jobs are able to queue message and dequeue it from the same queue.
My question is "Is there any way fix this issue without dropping and recreating the queue?"
Like will purging the queue solve this issue?
in general, a direct DELETE from a queue table is not allowed. The queue will then be corrupted!
>Like will purging the queue solve this issue?
Yes, there is a special PURGE statement for AQ, which should be used instead.
v_purge_options.block := FALSE;
queue_table => 'SCOTT.QTAB_TEST',
purge_condition => NULL,
purge_options => v_purge_options );