My Oracle Support Banner

Inconsistency when trying to create or drop an audit purge job using package DBMS_AUDIT_MGMT (Doc ID 2386675.1)

Last updated on MARCH 10, 2020

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

The following conflicting errors are raised when either trying to drop or create a purge job:

SQL> set echo on
SQL> @pj_drop
SQL> BEGIN
2 DBMS_AUDIT_MGMT.DROP_PURGE_JOB (
3 AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_OS_DB_AUDIT_RECORDS');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-27475: unknown job "SYS"."CLEANUP_OS_DB_AUDIT_RECORDS"
ORA-06512: at "SYS.DBMS_ISCHED", line 243
ORA-06512: at "SYS.DBMS_SCHEDULER", line 778
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1424
ORA-06512: at line 2

SQL> @pj_create
SQL>
SQL> BEGIN
2 DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
3 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
4 AUDIT_TRAIL_PURGE_INTERVAL => 2,
5 AUDIT_TRAIL_PURGE_NAME => 'CLEANUP_OS_DB_AUDIT_RECORDS',
6 USE_LAST_ARCH_TIMESTAMP => TRUE );
7 END;
8 /
BEGIN
*
ERROR at line 1:
ORA-46254: 'CLEANUP_OS_DB_AUDIT_RECORDS' already exists
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 177
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 3666
ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1245
ORA-06512: at line 2

 

Changes

As the most likely cause the purge job was dropped using package DBMS_SCHEDULER directly instead of using  DBMS_AUDIT_MGMT.DROP_PURGE_JOB, for example:

SQL> !cat pj_force_drop.sql
begin
dbms_scheduler.drop_job ('CLEANUP_OS_DB_AUDIT_RECORDS' , force => true);
end;
/

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.