How to migrate oracle audit logs to a new tablespace

 There was this situation when our oracle audit log suddenly exploded when one of the users ran a procedure. This caused the system tablespace to grow from 1G to 32G in a span of 8 hours.

This was caused by the audit table is in the system tablespace.

Below are the following steps to move the default audit log to a different tablespace.

Migrate the AUDIT tables to a dedicated tablespace

STEP 1

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

TABLE_NAME           TABLESPACE_NAME

-------------------- ------------------------------

AUD$                 SYSTEM

FGA_LOG$             SYSTEM

STEP 2

CREATE TABLESPACE

CREATE TABLESPACE ORA_AUDIT 

DATAFILE '/logs/oradata/EPWEBP/ora_audit1.dbf' 

SIZE 3G;

   

CREATE TABLESPACE ORA_AUDIT_ARCHIVE 

DATAFILE '/logs/oradata/EPWEBP/audit_archive1.dbf' 

SIZE 3G;

STEP 3

--this moves table FGA_LOG$

Run as system

BEGIN
  DBMS_AUDIT_MGMT.set_audit_trail_location(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    audit_trail_location_value => 'ORA_AUDIT');
END;
/

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

STEP 4 ARCHIVE CURRENT AUDIT LOG

CREATE TABLE AUDIT_10272020 TABLESPACE ORA_AUDIT_ARCHIVE as select * from SYS.AUD$;

TRUNCATE TABLE sys.aud$;