How to drop stuck purge job created through DBMS_AUDIT_MGMT

In the process of implementing automatic purging of the audit trail, I came across a problem that I was really puzzled by: In the debugging process I had to drop and recreate the purge job that I had previously created through the dbms_audit_mgmt package.

I was completely unable to drop a particular scheduler job. All attempts threw different errors:

Trying to drop the job through the dbms_audit_mgmt.drop_purge_job procedure:

dbms_audit_mgmt.drop_purge_job(audit_trail_purge_name=>'PURGE_ALL_AUDIT_TRAILS');

Then trying to drop it directly through the dbms_scheduler.drop_job procedure:

dbms_scheduler.drop_job(job_name=>'PURGE_ALL_AUDIT_TRAILS',force=>TRUE);

Both attempts returned the error:

ERROR at line 1:
ORA-27475: "SYS.PURGE_ALL_AUDIT_TRAILS" must be a job
ORA-06512: at "SYS.DBMS_ISCHED", line 224
ORA-06512: at "SYS.DBMS_SCHEDULER", line 657
ORA-06512: at line 2

Trying to enable the job explicitly, in case the scheduled job was in an unknown state to the database:

exec dbms_scheduler.enable(name=>'PURGE_ALL_AUDIT_TRAILS');

which resulted in

ERROR at line 1:
ORA-27476: SYS.PURGE_ALL_AUDIT_TRAILS does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 4440
ORA-06512: at "SYS.DBMS_SCHEDULER", line 2803
ORA-06512: at line 1

Trying to recreate the job:

BEGIN
    DBMS_AUDIT_MGMT.create_purge_job(
      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
      audit_trail_purge_interval => 24 /* hours */,
      audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
      use_last_arch_timestamp    => TRUE);
  EXCEPTION
    WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE(sqlerrm(sqlcode));
  END;
  /

Result:

ORA-46254: 'PURGE_ALL_AUDIT_TRAILS' already exists

Particuarly the last two error messages were discouraging. You cannot enable the job, because it doesn't exist, neither can you recreate the job, since it already DOES exist.

Solution:
From Oracle Support 1252235.1 "Unable to Create or Drop Purge Job Using DBMS_AUDIT_MGMT".

In short, log on as user sys and execute the following SQL statements:

SELECT JOB_NAME,STATE
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- No rows should be returned

SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL
FROM DBA_AUDIT_MGMT_CLEANUP_JOBS
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- 1 row should be returned, the job that's "stuck"

DELETE FROM SYS.DAM_CLEANUP_JOBS$
WHERE JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS'; <-- Delete the row from one of Oracle's internal tables,

COMMIT; <-- commit the change

And you're done, your job can be recreated, after which it will turn up as usual when you query the DBA_SCHEDULER_JOBS view:

SELECT STATE,ENABLED
FROM DBA_SCHEDULER_JOBS
WHERE  JOB_NAME = 'PURGE_ALL_AUDIT_TRAILS';

STATE	ENABLED
SCHEDULED	TRUE