Translate

Search This Blog

move audit tables aud$ and fga$ from system tablespace

Oracle 11g onwards:

New package DBMS_AUDIT_MGMT can be used to move audit base tables AUD$ and FG$ as below. AUDIT_DATA is the new tablespace where these audit tables have to be moved.

BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_DATA');
 END;
 
BEGIN
    DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_DATA');
 END;

Before oracle 11g :

Shutdown the system and start in restricted mode [ optional]

alter table aud$ move tablespace audit_data;

Create Index on the AUD$ table. [ optionall ]

create index audit_index
on aud$(sessionid, ses$tid)
tablespace audit_index;