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
Leave a Reply