Cleaning Up the Mail Queue in Oracle APEX

🚨 WARNING: DO NOT RUN THIS IN PRODUCTION!

Before executing any cleanup operations, ensure you are working in a development or test environment. Deleting the APEX mail queue in a production system can lead to data loss and disrupt email notifications.

📌 Introduction

Oracle APEX includes an internal mail queue for managing outbound emails. Over time, this queue can accumulate unwanted records, leading to delays in email processing and increased storage usage. This guide explains how to safely clean up the APEX mail queue while considering different execution privileges.

🔹 Why Clean Up the APEX Mail Queue?

✅ Fix stuck emails that are failing to send
✅ Free up storage space occupied by unnecessary emails
✅ Improve performance by reducing queue processing overhead
✅ Reset the mail queue after testing email functionalities

🔹 How to Check the Existing Mail Queue

Before deleting emails, check the existing records in the queue:


SELECT workspace_id, sent_date, mail_to, mail_from, 
mail_subj, mail_body
FROM apex_mail_queue
ORDER BY sent_date DESC;

📌 If this query returns too many records, it's a good indication that cleanup is needed.

🔹 Cleaning Up the APEX Mail Queue

The following PL/SQL block deletes all queued emails for all workspaces.


BEGIN
  -- WARNING: DO NOT RUN IN PRODUCTION!
  FOR r IN (
    SELECT workspace_id, workspace FROM apex_workspaces
  ) LOOP
    apex_application_install.set_workspace_id(r.workspace_id);
    apex_util.set_security_group_id(
      p_security_group_id => apex_application_install.get_workspace_id
    );
    DELETE FROM apex_mail_queue;
  END LOOP;
  
  COMMIT;
END;
/

🔍 Breakdown of the Script

Iterates through all APEX workspaces
Sets the correct workspace ID to ensure deletion occurs in the right context
Deletes all records from the apex_mail_queue table
Commits the changes to finalize the deletion

🔹 Verifying the Cleanup

After running the cleanup script, confirm that the mail queue is empty:


SELECT COUNT(*) FROM apex_mail_queue;

If the result is 0, then the cleanup was successful.

🔹 Additional Cleanup - Removing Stuck Mail Logs

If there are old mail log entries taking up space, you may also want to clean them up:


DELETE FROM apex_mail_log;
COMMIT;

🚀 Best Practices

✅ Run cleanup in non-production environments only
✅ Backup APEX before deleting any data
✅ Use scheduled jobs to manage mail queue retention

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Cleaning Up the Mail Queue in Oracle APEX