Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
The AUD$ and FGA_LOG$ tables used for audit recordings are in the SYSTEM tablespace by default. SYSTEM tablespace is a tablespace with manual segment space management. This causes problems with insertion into AUD$ table in 12c databases. It will also cause the SYSTEM table space to grow unnecessarily in heavily used databases.
It is recommended that these tables be moved to a different tableespace, which is segment space management auto.
You can view the current status with the following query.
1 | SQL> select ts.tablespace_name,t.table_name,ts.segment_space_management from dba_tables t, dba_tablespaces ts where ts.tablespace_name = t.tablespace_name and t.table_name in ('AUD |
You can create a new tablespace and move these two table to new tablespace.
Creating new table space
1 | SQL> CREATE TABLESPACE TS_AUDIT DATAFILE '+DATA' SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; |
Moving the AUD$ table to new tablespace
1 2 3 4 | SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,audit_trail_location_value => 'TS_AUDIT'); END; / |
Moving the FGA_LOG$ table to new tablespace
1 2 3 4 | SQL> BEGIN DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,audit_trail_location_value => 'TS_AUDIT'); END; / |
1 | ,'FGA_LOG |
Creating new table space
1 |
Moving the AUD$ table to new tablespace
1 |
Moving the FGA_LOG$ table to new tablespace
1 |
1 | ); TABLESPACE_NAME TABLE_NAME SEGMEN ----------------- --------------- ---------------- SYSTEM FGA_LOG$ MANUAL SYSTEM AUD$ MANUAL |
Creating new table space
1 |
Moving the AUD$ table to new tablespace
1 |
Moving the FGA_LOG$ table to new tablespace
1 |
