Check if Auditing is Enabled and How to Enable / Disable
Auditing is enabled or disabled by the parameter AUDIT_TRAIL.
In order to check if auditing is enabled/disabled, connect to DB as SYS (sysdba) via SQLPLUS and execute the command.
SHOW PARAMETER AUDIT_TRAIL
NAME TYPE VALUE
----------- ------ -----
audit_trail string DB
or the following SQL command:
SELECT * FROM V$PARAMETER WHERE NAME LIKE '%audit%';
Possible values for AUDIT_TRAIL are:
NONE - Audit trail is disabled
DB - Audit trail is enabled and records send to SYS.AUD$
OS - Audit trail is enabled and records send to file (.aud extension)
Every time the AUDIT_TRAIL is changed, the database instance must be rebounced for the parameter to take effect.
ALTER SYSTEM SET AUDIT_TRAIL = 'DB' SCOPE=SPFILE;
Extended Auditing
Enabling this will include auditing of SQL bind variables and values.
Examples,
ALTER SYSTEM SET AUDIT_TRAIL=DB,EXTENDED SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL='DB','EXTENDED' SCOPE=SPFILE;
ALTER SYSTEM SET AUDIT_TRAIL=XML,EXTENDED SCOPE=SPFILE;
Mandatory Auditing
Regardless of the settings of AUDIT_TRAIL, the following system operations are always audited and recorded in the OS audit trails:
- Database startup
- SYSDBA and SYSOPER logins
- Database shutdown
For Windows, these are recorded in the Windows Event Viewer (Application Events, the file is located in %WINDOWSROOT%\system32\winevt\logs). For Unix systems, these are recorded in the OS audit file, of which its location is determined by the AUDIT_FILE_DEST parameter (By default, AUDIT_FILE_DEST= $ORACLE_BASE/admin/$ORACLE_SID/adump).
Auditing Configuration Parameters
AUDIT_SYSLOG_LEVEL
This is for Unix-based system to send audit logs to the syslog of the Unix system.
AUDIT_SYS_OPERATIONS
By default this is set to FALSE. If TRUE, all operations by SYSDBA and SYSOPER will be audited. Audit records are written to the OS audit trails. It is written to XML file if AUDIT_TRAIL is set to XML.
Some initialization parameters cannot be altered using ALTER SYSTEM SET. In this case, you need to:
- Generate an editable iniSID.ora, then shutdown db instance.
- Edit the iniSID.ora to include the initialization parameter, then startup db instance
- Generate spfile
- Shutdown db instance and restart with new spfile
In SQLPLUS, login as SYSDBA,
create pfile='initSID.ora' from spfile;
Modify initSID.ora, add line,
*.audit_syslog_level=local0.info
Shutdown db instance. Backup existing spfile (located in e:\oracle\product\11.2.0\db_home\database).
Startup db instance using new pfile,
sqlplus sys/syspwd as sysdba
SQL> startup pfile='e:\oracle\admin\oradb\initSID.ora'
Generate new spfile from new pfile,
SQL> create spfile='e:\oracle\product\...\database\spfile.ora' from pfile='e:\oracle\admin\oradb\initSID.ora';
Verify spfile created successfully. Then, rebounce db instance with spfile.
sqlplus sys/syspwd as sysdba
SQL> startup
Check What is Being Audited and How to Enable / Disable Object, Privilege, Statement Audit
Check the following views to see what is being audited:
dba_obj_audit_opts - object auditing
dba_priv_audit_opts - privilege auditing
dba_stmt_audit_opts - statement auditing
In fact, there are already some basic auditing configured by default since the database was first installed.
In fact, there are already some basic auditing configured by default since the database was first installed.
You can use AUDIT and NOAUDIT commands to enable / disable specific auditing.
When BY USER is not specified, all users will be audited.
If neither WHEN SUCCESSFUL, nor WHEN NOT SUCCESSFUL is specified, both successful and unsuccessful actions will be audited.
When BY ACCESS is specified, a record is inserted into the audit trail for each statement issue.
DDL statements will be audited using BY ACCESS auditing, regardless of whether BY ACCESS or BY SESSION is specified.
Example to enable statement and privilege auditing:
AUDIT CONNECT, DELETE TABLE, INSERT TABLE,
EXECUTE ANY PROCEDURE
BY scott
BY ACCESS
WHENEVER SUCCESSFUL;
Example to disable statement and privilege auditing:
NOAUDIT CONNECT, DELETE TABLE, INSERT TABLE,
EXECUTE ANY PROCEDURE
BY scott
WHENEVER SUCCESSFUL;
Example to enable object auditing:
AUDIT SELECT, INSERT, DELETE
ON ownerx.tablex
WHENEVER SUCCESSFUL;
Example to disable object auditing:
NOAUDIT SELECT, INSERT, DELETE
ON ownerx.tablex
WHENEVER SUCCESSFUL;
Need Not Audit Failed Access
SQL> noaudit all;SQL> noaudit all privileges;
SQL> noaudit exempt access policy;
sql> audit all by access whenever successful; -- Add 27 AUDIT options
sql> audit alter any table by access whenever successful;
sql> audit create any table by access whenever successful;
sql> audit drop any table by access whenever successful;
sql> audit create any procedure by access whenever successful;
sql> audit drop any procedure by access whenever successful;
sql> audit alter any procedure by access whenever successful;
sql> audit grant any privilege by access whenever successful;
sql> audit grant any object privilege by access whenever successful;
sql> audit grant any role by access whenever successful;
sql> audit audit system by access whenever successful;
sql> audit create external job by access whenever successful;
sql> audit create any job by access whenever successful;
sql> audit create any library by access whenever successful;
sql> audit create public database link by access whenever successful;
sql> audit exempt access policy by access whenever successful;
sql> audit alter user by access whenever successful;
sql> audit create user by access whenever successful;
sql> audit role by access whenever successful;
sql> audit create session by access whenever successful;
sql> audit drop user by access whenever successful;
sql> audit alter database by access whenever successful;
sql> audit alter system by access whenever successful;
sql> audit alter profile by access whenever successful;
sql> audit drop profile by access whenever successful;
The standard audit trail report can be view via DBA_AUDIT_TRAIL view.
Audit Trail Properties (Audit Config Parameters)
The purging by the Audit Management Package performs a DELETE operation - not TRUNCATE, so the operation could generates lots of redo and undo records depending on the number of trail records deleted. A large deletion can potentially fill up the undo tablespace. The purge job deletes in batches of 1,000 and performs commits between them, so that the redo size is reduced based on the number of transaction commits. You can change the delete batch size by using the SET_AUDIT_TRAIL_PROPERTY procedure.Setting the deletion batch size example,
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;
/
In addition to the db_delete_batch_size property, you can use SET_AUDIT_TRAIL_PROPERTY to set other important properties. They include the following:
- file_delete_batch_size - specifies how many OS audit trail files will be deleted by the purge job in one batch.
- cleanup_interval specifies the default interval, in hours, between executions of a purge job.
- os_file_max_age specifies how many days an OS file or an XML file can be left open before a new file is created.
- os_file_max_size specifies the maximum size of an audit trail file (in kilobytes).
To find the current value of a property, you can check the data dictionary view DBA_AUDIT_MGMT_CONFIG_PARAMS.
set pagesize 150
set linesize 200
column parameter_name format a30
column parameter_value format a20
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
Fine Grained Auditing
This auditing is related to viewing and updating the table columns. Enabling this policies will make the audit trails grow really fast, so create this policies only if it is really necessary.
The FGA policies are stored in FGA$ and FGACOL$ tables, and the associated views are DBA_AUDIT_POLICIES and DBA_AUDIT_POLICY_COLUMNS. The FGA audit trails report are stored in FGA_LOG$ table, and the associated view is DBA_FGA_AUDIT_TRAILS.
You can create FGA audit policies like this:
EXEC DBMS_FGA.ADD_POLICY(
object_name=>'employees',
policy_name=>'check_salary',
audit_column=>'salary',
statement_types=>'select, update'
);
/
You can drop the policies like this:
EXEC DBMS_FGA.DROP_POLICY(
object_name=>'employees',
policy_name=>'check_salary'
);
/
DBA_COMMON_AUDIT_TRAIL displays all standard and fine grain audit trails records.
Audit Trails Location
On a typical setup, the audit trails are stored in the SYSTEM tablespace. If it gets full and unable to extend, the system will halt and unable to restart. Therefore, frequent purging of the audit trails is necessary. It is recommended to move the audit trails out of the SYSTEM tablespace to another tablespace for easier maintenance as well as reducing the risks of halting the entire system start up.
Audit Trails Purging with TRUNCATE or DELETE
Before purging the records in SYS.AUD$ (and possibly SYS.FGA_LOG$), you could backup selected records to a temporary backup tables, and reinsert them back after the purge. You could also export both the tables to a dump file as a backup.
Purging can be performed using the TRUNCATE or DELETE TABLE statement.
Purging can be performed using the TRUNCATE or DELETE TABLE statement.
create table myuser.aud$_bk as
select * from sys.aud$ where ntimestamp# > to_date('2017-01-01','YYYY-MM-DD');
create table myuser.fga_log$_bk as
select * from sys.fga_log$ where timestamp# > to_date('2017-01-01','YYYY-MM-DD');
truncate aud$;
insert into sys.aud$ select * from myuser.aud$_bk;
insert into sys.fga_log$ select * from myuser.fga_log$_bk;
drop table myuser.aud$_bk cascade constraints purge;
drop table myuser.fga_log$_bk cascade constraints purge;
Be aware that data pump expdp cannot export SYSTEM schemas such as SYS. Therefore, the traditional exp is being used.
DELETE TABLE could be used, but the UNDO tablespace will quickly fill up if the audit trails being deleted are large. TRUNCATE, on the other hand, does not generate undo information and therefore is recommended. Notice that the audit trails are still located in the SYSTEM tablespace.
It is possible to move the SYS.AUD$ table to a different tablespace using the ALTER TABLE MOVE statement. However, be aware that the table has LOB columns and the LOB resides on its own segment, outside of the tablespace that the table resides. You need to move the LOB objects separately using ALTER TABLE MOVE LOB statement. When you move the LOB segment, the LOB indexes move with it. Unfortunately, it is not possible to move FGA_LOG$ because it contains a LONG column, at least not with the ALTER TABLE MOVE statement. There is a bit of trouble here, so it is not recommended to move them using this statement. If you must move them out of SYSTEM tablespace, you should use the procedures provided by the Audit Management Package.
It is not possible to shrink tables inside the SYSTEM tablespace (e.g. ALTER TABLE SYS.AUD$ SHRINK SPACE). So, don't bother trying.
DELETE TABLE could be used, but the UNDO tablespace will quickly fill up if the audit trails being deleted are large. TRUNCATE, on the other hand, does not generate undo information and therefore is recommended. Notice that the audit trails are still located in the SYSTEM tablespace.
It is possible to move the SYS.AUD$ table to a different tablespace using the ALTER TABLE MOVE statement. However, be aware that the table has LOB columns and the LOB resides on its own segment, outside of the tablespace that the table resides. You need to move the LOB objects separately using ALTER TABLE MOVE LOB statement. When you move the LOB segment, the LOB indexes move with it. Unfortunately, it is not possible to move FGA_LOG$ because it contains a LONG column, at least not with the ALTER TABLE MOVE statement. There is a bit of trouble here, so it is not recommended to move them using this statement. If you must move them out of SYSTEM tablespace, you should use the procedures provided by the Audit Management Package.
It is not possible to shrink tables inside the SYSTEM tablespace (e.g. ALTER TABLE SYS.AUD$ SHRINK SPACE). So, don't bother trying.
Audit Trails Purging using Audit Management Package
The audit management package includes procedures that automatically perform the purge and the moving of AUD$ out of the SYSTEM tablespace. Before this package can be used, you must call a one-time initialization procedure, the INIT_CLEANUP.
dbms_audit_mgmt.init_cleanup (
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
default_cleanup_interval => 720);
end;
/
The INIT_CLEANUP sets the cleanup frequency (number of hours), at the same time, it moves the audit trails out of the SYSTEM tablespace. The cleanup frequency only takes effect if automatic cleanup job is created. The INIT_CLEANUP by itself does not create jobs.
If SET_AUDIT_TRAIL_LOCATION procedure was not called before INIT_CLEANUP, the audit trails will be moved from the SYSTEM to the SYSAUX tablespace. If this is want you want, make sure the SYSAUX tablespace has sufficient space to hold both AUD$ and FGA_LOG$ tables (resize the datafile if necessary).
If you want the audit trails to be stored in a separate tablespace, you must call the SET_AUDIT_TRAIL_LOCATION procedure before calling INIT_CLEANUP.
Tips:
Find out the size of AUD$ from the dba_segments table:
SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$';
Or, just create the new tablespace with a size similar to the size of the SYSTEM tablespace. Later, after the AUD$ (and FGA_LOG$) is truncated and shrinked, resize the datafile.
create tablespace AUDIT_TS datafile 'e:\oradata\orasid\AUDIT_TS.DBF' size 1G autoextend on next 1M maxsize unlimited;
Consider a uniform tablespace too:
CREATE TABLESPACE AUDTBS DATAFILE 'e:\oradata\orasid\AUDIT_TS.DBF' SIZE 128M
AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO;
begin
dbms_audit_mgmt.set_audit_trail_location (
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
audit_trail_location_value => 'AUDIT_TS');
end;
/
The execution of this procedure could take from minutes to hours depending on how much data is moved. It moves everything including the LOB segments and all the indexes. So, be patient and advised to perform this off working hours.
Recommendation: perform purge only during off hours because this operation takes a very long time if the audit trail is large (warning: could take hours).
begin
dbms_audit_mgmt.clean_audit_trail (
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
use_last_arch_timestamp => true);
end;
/
If there is no records in FGA_LOG$, set audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std.
If the last archive timestamp is not set, or has been cleared, it will purge the entire audit trails.
select * from dba_audit_mgmt_last_archive_ts;
no rows selected
If required, the last archive timestamp can be cleared using the following procedure:
begin
dbms_audit_mgmt.clear_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_all);
end;
/
The last archive timestamp can be set using the following procedure:
begin
dbms_audit_mgmt.set_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time => to_timestamp('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null);
end;
/
If SET_AUDIT_TRAIL_LOCATION procedure was not called before INIT_CLEANUP, the audit trails will be moved from the SYSTEM to the SYSAUX tablespace. If this is want you want, make sure the SYSAUX tablespace has sufficient space to hold both AUD$ and FGA_LOG$ tables (resize the datafile if necessary).
If you want the audit trails to be stored in a separate tablespace, you must call the SET_AUDIT_TRAIL_LOCATION procedure before calling INIT_CLEANUP.
Changing Audit Trails Tablespace
Create a new tablespace that is large enough for the move, otherwise SET_AUDIT_TRAIL_LOCATION will fail even if AUTOEXTEND ON is set.Tips:
Find out the size of AUD$ from the dba_segments table:
SELECT SUM(BYTES) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='AUD$';
Or, just create the new tablespace with a size similar to the size of the SYSTEM tablespace. Later, after the AUD$ (and FGA_LOG$) is truncated and shrinked, resize the datafile.
create tablespace AUDIT_TS datafile 'e:\oradata\orasid\AUDIT_TS.DBF' size 1G autoextend on next 1M maxsize unlimited;
Consider a uniform tablespace too:
CREATE TABLESPACE AUDTBS DATAFILE 'e:\oradata\orasid\AUDIT_TS.DBF' SIZE 128M
AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED
NOLOGGING default NOCOMPRESS ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M SEGMENT SPACE MANAGEMENT AUTO;
begin
dbms_audit_mgmt.set_audit_trail_location (
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
audit_trail_location_value => 'AUDIT_TS');
end;
/
The execution of this procedure could take from minutes to hours depending on how much data is moved. It moves everything including the LOB segments and all the indexes. So, be patient and advised to perform this off working hours.
Audit Trails Purging (Manual)
Call the CLEAN_AUDIT_TRAIL procedure to perform the actual purge.Recommendation: perform purge only during off hours because this operation takes a very long time if the audit trail is large (warning: could take hours).
begin
dbms_audit_mgmt.clean_audit_trail (
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
use_last_arch_timestamp => true);
end;
/
If there is no records in FGA_LOG$, set audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std.
If the last archive timestamp is not set, or has been cleared, it will purge the entire audit trails.
Last Archive Timestamp
Check the last archive timestamp:select * from dba_audit_mgmt_last_archive_ts;
no rows selected
If required, the last archive timestamp can be cleared using the following procedure:
begin
dbms_audit_mgmt.clear_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_all);
end;
/
The last archive timestamp can be set using the following procedure:
begin
dbms_audit_mgmt.set_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time => to_timestamp('2017-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null);
end;
/
Or use sysdate - 14 to keep records for the last 14 days.
begin
dbms_audit_mgmt.set_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time => trunc(systimestamp)-14,
rac_instance_number => null);
dbms_audit_mgmt.set_last_archive_timestamp (
audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std,last_archive_time => trunc(systimestamp)-14,
rac_instance_number => null);
end;
/
After the purge is completed, check the HWM of the tablespace. Shrink the SYS.AUD$ table if necessary and resize the datafile. Shrinking AUD$ is not possible if it is still in the SYSTEM tablespace. You can shrink the tablespace as follow:
alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;
CREATE OR REPLACE PROCEDURE SP_AUDIT_PURGE_ALL
AS
BEGIN
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
);
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
Create a store procedure to purge partial records:
CREATE OR REPLACE PROCEDURE SP_AUDIT_PURGE_PARTIAL
AS
retention_days NUMBER;
BEGIN
retention := 14;
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => TRUNC(SYSTIMESTAMP) - retention_days
);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
LAST_ARCHIVE_TIME => TRUNC(SYSTIMESTAMP) - retention_days
);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/
Create scheduler job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_AUDIT_PURGE_PARTIAL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SP_AUDIT_PURGE_PARTIAL(); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; interval=14; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job to purge audit trails partially'
);
END;
/
alter table sys.aud$ enable row movement;
alter table sys.aud$ shrink space cascade;
Automatic Purging of Audit Trails
Create a stored procedure to purge all records:CREATE OR REPLACE PROCEDURE SP_AUDIT_PURGE_ALL
AS
BEGIN
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD
);
DBMS_AUDIT_MGMT.CLEAR_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD
);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE
);
END;
/
Create a store procedure to purge partial records:
CREATE OR REPLACE PROCEDURE SP_AUDIT_PURGE_PARTIAL
AS
retention_days NUMBER;
BEGIN
retention := 14;
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
LAST_ARCHIVE_TIME => TRUNC(SYSTIMESTAMP) - retention_days
);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
LAST_ARCHIVE_TIME => TRUNC(SYSTIMESTAMP) - retention_days
);
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_DB_STD,
USE_LAST_ARCH_TIMESTAMP => TRUE);
END;
/
Create scheduler job:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'JOB_AUDIT_PURGE_PARTIAL',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN SP_AUDIT_PURGE_PARTIAL(); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; interval=14; byhour=0; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Job to purge audit trails partially'
);
END;
/
You can view the jobs from the dba_scheduler_jobs view.
You could create purge job with DBMS_AUDIT_MGMT.CREATE_PURGE_JOB. But remember to create a scheduler job to set the last archive timestamp before the purge action.
begin
dbms_audit_mgmt.create_purge_job (
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
audit_trail_purge_interval => 24*14, /* hours */
audit_trail_purge_name => 'std_audit_trail_purge_job',
use_last_arch_timestamp => TRUE
);
end;
/
Create scheduler job to set last archive timestamp:
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'audit_last_archive_time',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-14);
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-14);
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=23; byminute=0; bysecond=0;',
end_date => NULL,
enabled => TRUE,
comments => 'Automatically set audit last archive time everyday at 11pm.');
END;
/
You can view important information on purge jobs from DBA_AUDIT_MGMT_CLEANUP_JOBS.
SELECT JOB_NAME,JOB_STATUS,AUDIT_TRAIL,JOB_FREQUENCY FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
