Oracle 12c – Deleting Rows from user_scheduler_job_run_details Table

There is an exception. When you define a before insert, row-level trigger on a table and issue a single row INSERT statement, the table is mutating error will not be raised. But if you define the same kind of trigger and issue a multi-row INSERT statement, the error will be raised. Here is an example:

SQL> create table TB_TR_TEST(
  2    col1 number,
  3    col2 number
  4  )
  5  ;

Table created

SQL> create or replace trigger TR_TB_TR_TEST
  2  before insert on TB_TR_TEST
  3  for each row
  4  begin
  5    SELECT max(col1) + 1 INTO :NEW.col1
  6      FROM TB_TR_TEST;
  7    UPDATE TB_TR_TEST SET col2 = 5000;
  8  end;
  9  /

Trigger created

Here is a single-row insert statement, which won't raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2) values(1,2);

1 row inserted

SQL> insert into TB_TR_TEST(col1, col2) values(3,5);

1 row inserted

SQL> commit;

Commit complete

Here is a multi-row insert statement, which will raise mutating table error:

SQL> insert into TB_TR_TEST(col1, col2)
  2    select 1, 2
  3      from dual;

insert into TB_TR_TEST(col1, col2)
  select 1, 2
    from dual

ORA-04091: table HR.TB_TR_TEST is mutating, trigger/function may not see it
ORA-06512: at "HR.TR_TB_TR_TEST", line 2
ORA-04088: error during execution of trigger 'HR.TR_TB_TR_TEST'

As Mat has pointed out, I hit Bug 9164488 (DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL does not delete sys.aud$ and sys.fga_log$ tables [ID 9164488.8]) which should be fixed in Release 11.2.0.2.

In the meantime, running on 11.2.0.1, I was able to circumvent the bug with a purge job in order to do the work. As soon as I ran

begin
  sys.dbms_audit_mgmt.create_purge_job (
     audit_trail_type           => sys.dbms_audit_mgmt.audit_trail_fga_std,
     audit_trail_purge_interval => 999,
     audit_trail_purge_name     =>'Purge_Test',
     use_last_arch_timestamp    => true
  );
end;
/

clean_audit_trail would do the work, as previously expected.