Moving Audit Trail and Fine Grained Auditing Log Table to new tablespace

Create a new tablespace

CREATE tablespace "SYSAUD" datafile size 1G autoextend on;


Move Audit Trail Table SYS.AUD$ to new tablespace


exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'SYSAUD')

Move FGA_LOG$ table to new tablespace

exec DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,audit_trail_location_value => 'SYSAUD');

Check new location of both tables

SELECT owner, table_name, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name; 

Check new location of indexes associated with above tables


SELECT owner, index_name, index_type, table_owner, table_name, tablespace_name
FROM dba_indexes
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name;

Set Audit Trail purge interval in hours (168=7 days(24*7)

Parameters for Audit type are listed below

Parameter          Description

audit_trail_aud_std The standard AUD$ audit trail in the database
audit_trail_fga_std The FGA_LOG$ table, for Fine Grained Auditing
audit_trail_db_std Both standard and FGA audit trails
audit_trail_os            The OS audit trail
audit_trail_xml           The XML audit trail
audit_trail_files         Both OS and XML audit trails
audit_trail_all All of the above

begin
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_db_std,
default_cleanup_interval => 168 );
end;


Disabling Auditing

begin
    for c1 in (select * from dba_audit_policies) loop
        dbms_fga.drop_policy (
            object_schema => c1.object_schema,
            object_name   => c1.object_name,
            policy_name   => c1.policy_name);
    end loop;
end;
/


Enable auditing for entire schema:

begin
    for c1 in (select * from all_tables where owner = '&schema') loop
        dbms_fga.add_policy (
            object_schema   => c1.owner,
            object_name     => c1.table_name,
            statement_types => 'SELECT,UPDATE,DELETE,INSERT',
            policy_name     => c1.table_name
        );
    end loop;
end;

/