9 Replies Latest reply: Oct 11, 2007 5:24 PM by user591719 RSS

    Cleaning queues

    450470
      Does anyone know a prodedure to clean a queue (any of them) ?

      (in fact, what REALLY lacks is an administrator tool to view and edit any user mailboxes, and the same for the queues !)
        • 1. Re: Cleaning queues
          399322
          What I have found that works is to set the status of the queued message to '3' which will then cause the garbage collector to remove it.

          update es_queue set msg_state = 3 where msg_id = ?
          update es_queue set msg_state = 3 where queue = ?

          Tracking down the relevant msg_id is the hard part, check out the procedure written by Paul Nock in an earlier post:
          reviewing mail queues?

          I made a view out of this code which makes it a bit easier to display, you might want to adjust the dates as I have altered it to display in GMT+8:

          CREATE OR REPLACE VIEW QUEUE_MESSAGES ( MSG_ID,
          QUEUE, QUEUE_NAME, QUEUE_DATE, SECONDS_IN_QUEUE,
          SENDER, STATE, STATE_NAME, SUBJECT,
          MSG_SIZE, RECIPIENTS ) AS select es_queue.msg_id msg_id
          ,es_queue.queue
          ,decode(es_queue.queue, 1, 'Submit', 2, 'Relay', 3, 'Local', 5, 'List', 6, 'News', 'Unknown') queue_name
          ,to_char(es_header.internal_date + 8 * 1/24, 'Dy Mon DD HH24:MI') queue_date
          ,60 * 60 * 24 * (sysdate - (es_header.internal_date + 8 * 1/24)) seconds_in_queue
          ,es_header.rfc822_from sender
          ,es_queue.msg_state state
          ,decode(es_queue.msg_state, 1, 'New', 2, 'Processing', 3, 'Done', 4, 'Moderation', 'Unknown') state_name
          ,es_header.rfc822_subject subject
          ,es_header.msg_size msg_size
          ,es_header.rfc822_to recipients
          from es_header
          ,es_queue
          where es_header.msg_id = es_queue.msg_id
          order by msg_id

          As for viewing another user's mailbox, this sort of functionality would be really handy. You can query the mail tables directly, eg. ES_FOLDER, ES_HEADER, ES_BODY, but that's a lot of work. It would be nice to also administer the user's mailbox as well - the ability to assign privileges on a folder by an administrator is sorely missing.
          • 2. Re: Cleaning queues
            432524
            Hi folks!
            The process of changing the status of the queue flag works (did it more than once) but it is absolutely not supported. So be aware that if you do this at a production site you might need a good backup.

            And I agree that there are somethings that the administrator role should have:
            - cleaning queues
            - working on other peoples mailboxes
            - temporary password changes

            Maybe we can compile a wishlist for the next release :-)

            cu
            Andreas
            • 3. Re: Cleaning queues
              450470
              Wishlist surely, but we need to be many to be taken seriously. Every other professional mail system actually has this sort of features. I know ppl don't choose OCS for the email ony, but administrators still have a hard work.

              I'll add to this list :
              - clean up the administration tools, because some features are present BOTH on email client (the old one) and on OCS Control
              - integrate the administration features into WAC
              • 4. Re: Cleaning queues
                432524
                Hi Arnaud!
                I send this list to the OCS director in Europe. Let's see what happens.

                cu
                Andreas
                • 5. Re: Cleaning queues
                  450470
                  You mean David R. ?
                  • 6. Re: Cleaning queues
                    432524
                    No, I sent it to Herman S. :-)

                    cu
                    Andreas
                    • 7. Re: Cleaning queues
                      450470
                      Gosh, I guess threre's too many OCS directors for europe in that company ;-)
                      • 8. Re: Cleaning queues
                        user591719
                        I don't know how it is possible since Des_queue is a view and the column msg_state doesn't exists it is a constant the view is something like this                    SELECT msg_id, queue, 1 msg_state, delivery_prio,
                                                 modified_date, delivery_count, flags
                                            FROM new_queue
                                       UNION
                                            SELECT msg_id, queue, 2 msg_state, delivery_prio,
                                                 modified_date, delivery_count, flags
                                            FROM inproc_queue
                                       UNION
                                            SELECT msg_id, queue, 3 msg_state, 0 delivery_prio,
                                                 null modified_date, 0 delivery_count, 0 flags
                                            FROM done_queue
                        • 9. Re: Cleaning queues
                          user591719
                          this is what i did and works, run this script:

                          DECLARE
                          l_mids DBMS_SQL.number_table;
                          x_mids DBMS_SQL.number_table;
                          BEGIN
                          SELECT msg_id
                          BULK COLLECT INTO l_mids
                          FROM new_queue
                          WHERE queue = 2;

                          mail_admin.delete_queue_msgs (2, l_mids, x_mids);
                          COMMIT;
                          DBMS_OUTPUT.put_line ('got ' || l_mids.COUNT || ' messages in queue 2');
                          DBMS_OUTPUT.put_line ('deleted ' || x_mids.COUNT
                          || ' messages from queue 2'
                          );
                          END;
                          /