Jump to Navigation

300 - Move Audit Trail Tables

1. Tablespace Check

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

 

2. Create a new table space for Audit Trail

SQL> CREATE TABLESPACE AUDIT_AUX DATAFILE '/u01/app/oracle/oradata/mydb/audit_aux01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;

 

3. Move Audit Tablespace to the new location

BEGIN
  SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;

BEGIN
  SYS.DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
    audit_trail_location_value => 'AUDIT_AUX');
END;
/

 

4. Try to resize SYSTEM tablespace

ALTER database datafile '/u01/app/oracle/oradata/mydb/system02.dbf' resize 10M;

You can resize it depending on the blocks usage.

 

 

 



Main menu 2

Story | by Dr. Radut