Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT

Symptoms

When attempting to create a new purge job,
the following error occurs.

ERROR
———————–
ORA-46252: Cannot complete the operation, overlapping job already exists
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 61
ORA-06512: at “SYS.DBMS_AUDIT_MGMT”, line 2595
ORA-06512: at line 2


STEPS
———————–
The issue can be reproduced at will with the following steps:
1. sqlplus / as sysdba

2. BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
  AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
  AUDIT_TRAIL_PURGE_INTERVAL => 24,
  AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
  USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

Cause

DBA_SCHEDULER_JOB does not have the information about the audit_mgmt job.
 
This scenario happens when DBMS_SCHEDULER and DBMS_AUDIT_MGMT are not Synchronous .
 

Solution

SQL> DELETE FROM SYS.DAM_CLEANUP_JOBS$ where JOB_NAME = ‘STANDARD_OS_AUDIT_TRAIL_PURGE’;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => ‘PURGE_ALL_AUDIT_TRAILS’,
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

PL/SQL procedure successfully completed.
 

References

NOTE:1252235.1 – Unable To Create Or Drop Purge Job Using DBMS_AUDIT_MGMT