This discussion is archived
9 Replies Latest reply: Oct 11, 2007 3:24 PM by user591719 RSS

Cleaning queues

450470 Explorer
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    Hi Arnaud!
    I send this list to the OCS director in Europe. Let's see what happens.

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

    cu
    Andreas
  • 7. Re: Cleaning queues
    450470 Explorer
    Currently Being Moderated
    Gosh, I guess threre's too many OCS directors for europe in that company ;-)
  • 8. Re: Cleaning queues
    user591719 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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;
    /