1 Reply Latest reply: Jul 3, 2013 3:46 AM by user648708 RSS

    Manual deletion from queue table

    samujjwalbasu

      Hi All,

       

      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?

       

      Regards,

       

      Samujjwal Basu

        • 1. Re: Manual deletion from queue table
          user648708

          Hello,

           

          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.

           

          Example:

           

          DECLARE

           

             v_purge_options    dbms_aqadm.aq$_purge_options_t;

           

          BEGIN

           

             v_purge_options.block := FALSE;

           

             DBMS_AQADM.PURGE_QUEUE_TABLE(

               queue_table     => 'SCOTT.QTAB_TEST',

               purge_condition => NULL,

               purge_options   => v_purge_options );

           

          END;

          /

           

          Kind regards,

           

          WoG