11.2.0.1版本审计表存放在SYSTEM

11.2.0.2版本后审计表存放在SYSAUX

参考资料：

https://oracle-base.com/articles/11g/auditing-enhancements-11gr2

[sql]

CREATE TABLESPACE audit_aux
DATAFILE ‘ +DATA/xxxx/datafile/audit_aux01.dbf’
SIZE 1M AUTOEXTEND ON NEXT 1M;
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘AUDIT_AUX’);
END;
/

PL/SQL procedure successfully completed.

SQL>

— Check locations.
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’)
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
—————————— ——————————
AUD$ AUDIT_AUX
FGA_LOG$ SYSTEM

SQL>
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => ‘AUDIT_AUX’);
END;
/

PL/SQL procedure successfully completed.

SQL>

— Check locations.
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN (‘AUD$’, ‘FGA_LOG$’)
ORDER BY table_name;

TABLE_NAME TABLESPACE_NAME
—————————— ——————————
AUD$ AUDIT_AUX
FGA_LOG$ AUDIT_AUX
BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
default_cleanup_interval => 12 /* hours */);
END;
/
PL/SQL procedure successfully completed.
SQL>

SELECT * FROM dba_audit_mgmt_config_params;

PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
—————————— ——————– ——————–
DB AUDIT TABLESPACE SYSTEM STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSTEM FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 OS AUDIT TRAIL

PARAMETER_NAME PARAMETER_VALUE AUDIT_TRAIL
—————————— ——————– ——————–
DEFAULT CLEAN UP INTERVAL 12 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 XML AUDIT TRAIL
BEGIN
DBMS_AUDIT_MGMT.set_last_archive_timestamp(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => SYSTIMESTAMP-5);
END;
/

SELECT * FROM dba_audit_mgmt_last_arch_ts;

AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS
——————– ———— —————————————-
STANDARD AUDIT TRAIL 0 13-DEC-09 01.57.54.000000 PM +00:00

SQL>

SELECT COUNT(*) FROM aud$;

COUNT(*)
———-
2438

SQL>
BEGIN
DBMS_AUDIT_MGMT.clean_audit_trail(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/

PL/SQL procedure successfully completed.

SELECT COUNT(*) FROM aud$;

COUNT(*)
———-
76
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);
END;
/

PL/SQL procedure successfully completed.

SQL>

SELECT job_action
FROM dba_scheduler_jobs
WHERE job_name = ‘PURGE_ALL_AUDIT_TRAILS’;

JOB_ACTION
——————————————————————————–
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE); END;

SQL>

[/sql]