Currently only the Apex Instance Administrator (the super administrator of the INTERNAL workspace) is able to purge the log from the apex mail log maintenance page without any options to selectively delete the data for a specific workspace or by date.
According to my experience and needs I would like to be able to purge the log for a certain workspace more frequently than others and/or remove failed emails earlier than successful ones.
That is why, while I wait for my dreams to come true, I set out and wrote the following procedure that enables me to schedule this housekeeping task and let the computer do the dirty work in my place.
In the end this is why computers were invented in the first place, isn't it?
-- the DBA is supposed to create this procedure in the APEX_TOOLS repository schema. create or replace procedure apex_tools.purge_apex_mail_log ( p_wks in varchar2, -- workspace name p_older in date default null, -- optional date limit p_error_contains in varchar2 default null) -- optional error match string authid current_user -- invoker rights as l_sgid number := apex_custom_auth.get_security_group_id; l_dummy char(2); begin /* ** USE AT YOUR OWN RISK ** Create by FC 25/7/2013 Requirements: Version 1.01 1) an APEX_TOOLS repository user must have been created: CREATE USER APEX_TOOLS IDENTIFIED BY "APEX_TOOLS" DEFAULT TABLESPACE <tablespace> ACCOUNT LOCK; 2) SGID must be set in advance: APEX_UTIL.SET_SECURITY_GROUP_ID(APEX_UTIL.FIND_SECURITY_GROUP_ID('<wks>')); 3) caller must have GRANT DELETE rights on APEX_MAIL_LOG
4) caller must have GRANT EXECUTE on APEX_TOOLS.PURGE_APEX_MAIL_LOG
This procedure purges the portion of apex_mail_log belonging to the workspace that the executing user (oracle schema) is associated with. NB: the SGID (Security Group ID) must be set BEFORE calling this procedure and must match with the ID associated with the symbolic name of the workspace name you pass as parameter. The checks performed on the privileges held by the user running the procedure are meant to avoid confusion and report errors in case of a misconfiguration of the parameters. The procedure does NOT attempt to set the security ID basing on the user executing the procedure because that practice is forbidden by the API setting the SGID when run with invoker rights. */ if l_sgid = 0 then raise_application_error(-20001, 'Security Group ID is not set'); else begin /* verify that the invoker is associated with the workspace by name and by SGID */ select 'ok' into l_dummy from apex_workspace_schemas where schema = user and workspace_id = l_sgid and workspace_name = p_wks; exception when no_data_found then /* the query returned no rows, so you don't hold any rights on the workspace passed as parameter or you entered a wrong workspace name that doesn't match with the SGID currently set */ raise_application_error(-20002, 'You are not authorized to access this workspace'); end; end if; /* delete the entries associated with the SGID currently set NB: this will work only if the invoker holds GRANT DELETE on APEX_MAIL_LOG otherwise a run time ORA-01031 error will be raised. */ execute immediate 'delete from apex_mail_log where (:p_older is null or last_updated_on < :p_older) and (:p_error_contains is null or instr(mail_send_error, :p_error_contains)>0)' using p_older, p_older, p_error_contains, p_error_contains; /* reports the number of deleted rows when called interactively */ dbms_output.put_line('log entries deleted: ' || to_char(SQL%ROWCOUNT)); end; / -- the DBA must give execute rights for executing this procedure to public -- or to specific users GRANT EXECUTE ON apex_tools.purge_apex_mail_log TO PUBLIC; -- allow selected users to perform the purging of log GRANT DELETE ON apex_mail_log to <user1>, <user2>...;
Now you are finally ready to schedule the distincts jobs for each of the relevant workspaces.
Log in as the primary schema user associated to the desired workspace and run this script (the user must have CREATE JOB rights). The script will create a job scheduled at the specified hour starting from the next day.
set verify off accept WKS PROMPT "workspace name:" accept DAYS NUMBER PROMPT "delete entries older than (number of days):" accept HOUR NUMBER PROMPT "enter time of job start (hour 0-23):" begin DBMS_SCHEDULER.CREATE_JOB ( job_name => 'PURGE_APEX_MAIL_LOG_JOB', job_type => 'PLSQL_BLOCK', job_action => 'begin apex_util.set_security_group_id(apex_util.find_security_group_id(''&WKS'')); apex_tools.purge_apex_mail_log(''&WKS'',trunc(sysdate)-&DAYS); commit; end;', start_date => trunc(sysdate)+1+1/(24/&HOUR), repeat_interval => 'FREQ=DAILY', enabled => true, comments => lower('purge &wks workspace mail log') ); end;/