show parameter audit
-- check tablespaces
SELECT table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;
-- when last deleted
SELECT * FROM dba_audit_mgmt_last_arch_ts;
-- total # of records
SELECT COUNT(*) FROM aud$;
SCOTT_AUDIT is tablespace name
-- change for AUD$
BEGIN
DBMS_AUDIT_MGMT.set_audit_
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_
audit_trail_location_value => 'SCOTT_AUDIT');
END;
/
-- change for FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_
audit_trail_location_value => 'SCOTT_AUDIT');
END;
/
-- both in single step
BEGIN
DBMS_AUDIT_MGMT.set_audit_
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_
audit_trail_location_value => 'SCOTT_AUDIT');
END;
/
-- Controlling the Size and Age of the OS Audit Trail
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SELECT * FROM dba_audit_mgmt_config_params
-- Purging Audit Trail Records
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SELECT * FROM dba_audit_mgmt_config_params;
BEGIN
DBMS_AUDIT_MGMT.init_cleanup(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_
default_cleanup_interval => 12 /* hours */);
END;
/
SET SERVEROUTPUT ON
BEGIN
IF DBMS_AUDIT_MGMT.is_cleanup_
DBMS_OUTPUT.put_line('YES');
ELSE
DBMS_OUTPUT.put_line('NO');
END IF;
END;
/
-- how much data you wish to purge
COLUMN audit_trail FORMAT A20
COLUMN last_archive_ts FORMAT A40
SELECT * FROM dba_audit_mgmt_last_arch_ts;
-- to deleted manualy, must run DBMS_AUDIT_MGMT.init_cleanup before this
BEGIN
DBMS_AUDIT_MGMT.clean_audit_
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_
use_last_arch_timestamp => FALSE);
END;
/
