ASSM : AUD$ and FGA_LOG$

With AUDIT_TRAIL set for database  AUD$ and FGA_LOG$ tables located in a dictionary segment space managed SYSTEM tablespace,
“gc” wait events are sometimes observed during heavy periods of database logon activity. Testing has shown that under such conditions,
placing the AUD$ and FGA_LOG$ tables in the SYSAUX tablespace, which uses automatic segment space management, reduces the space related wait events.

Moving them to the SYSAUX tablespace doesn’t require an outage.

Source the enviroment as Oracle User

sqlplus / as sysdba

SQL>  select 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$’,’FGA_LOG$’);

TABLE_NAME                     SEGMEN
—————————— ——
FGA_LOG$                       MANUAL
AUD$                           MANUAL

Currently the AUD$ and FGA_LOG$ are not pointed to Automated Segemen Space Tablespace.

BEGIN

DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => ‘SYSAUX’);
END;
/

BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => ‘SYSAUX’);
END;

The above calls will move the AUD$ and FGA_LOG$ to SYSAUX.

Confirm by querying again.

select 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$’,’FGA_LOG$’);

TABLE_NAME                     SEGMEN
—————————— ——
FGA_LOG$                       AUTO
AUD$                           AUTO

Related Posts: