Oracle Audit and how to clear off Oracle Audit

select user_name, audit_option, success, failure
        from sys.dba_stmt_audit_opts;
     
select sessionid, to_char(ntimestamp#,'DD-MON-YY:HH24:MI:SS') login,
userid, to_char(logoff$time,'DD-MON-YY:HH24:MI:SS') logoff
from sys.aud$ where userid=<USER_NAME>';

select username, priv_used, ses_actions from dba_audit_object;

select action, action_name, username from dba_audit_trail ;

select * from stmt_audit_option_map;


clear Audit :-


begin
  dbms_audit_mgmt.init_cleanup(
    audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval    => 7*24 );
end;
/

Next, to perform the actual purge, you have to execute a packaged procedure dbms_audit_mgmt.clean_audit_trail().

begin
  dbms_audit_mgmt.clean_audit_trail(
   audit_trail_type =>  dbms_audit_mgmt.audit_trail_all
);
end;
/

Beware: this performs a DELETE operation so there will be a lot of redo and undo. When the initial purge is complete, you may want to set up an automated process to execute this periodically. You will need to create a DBMS Scheduler Job as shown below. This job will be run every week.

begin
   dbms_audit_mgmt.create_purge_job (
   audit_trail_type            => dbms_audit_mgmt.audit_trail_all,
   audit_trail_purge_interval  => 7*24,
   audit_trail_purge_name      => 'all_audit_trails_job'
   );
end;
/

Note the parameter audit_trail_type which is set to dbms_audit_mgmt.audit_trail_all, which means all the audit trails: AUD$, SYS Audit, FGA audit trails, OS files and XML files. You can also specify those trails specifically as well.

to set a property for  type of Database Audit trails trail  give:-

begin
 dbms_audit_mgmt.set_audit_trail_property(
  audit_trail_type           => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_property       => dbms_audit_mgmt.db_delete_batch_size,
  audit_trail_property_value => 100000);
end;
/