Tag Archives: DBMS_AUDIT_MGMT

Move Audit table AUD$ & FGA_LOG$ from SYSAUX to New tablespace

Move audit table AUD$ & FGA_LOG$ from SYSAUX tablespace to new in Oracle

Following are the steps to move the Audits table from SYSAUX default tablespace to new created tablespace.

1. Check the current tablespace for both tablespace

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

TABLE_NAME TABLESPACE_NAME
---------- -----------------
AUD$ SYSTEM
FGA_LOG$ SYSTEM

2. Check the size of tablespace

column segment_name for a10
select segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

SEGMENT_NA SIZE_IN_MEGABYTES
---------- -----------------
AUD$ .0625
FGA_LOG$ .0625

3. Check the exisiting file location for database

select file_name from dba_data_files;

FILE_NAME
------------------------------------------------
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF
D:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF

4. Create a new tablespace for audit records and keep autoextend on for it.

create tablespace audit_tbs datafile 'D:\ORACLEXE\APP\ORACLE\ORADATA\XE\audit_db.dbf' size 100M autoextend on;

5. Move the audit trail tables using executing the procedure as follows:

--this moves table AUD$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'AUDIT_TBS');
END;
/

--this moves table FGA_LOG$
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/

Note: It will take time depend upto data and speed of your system.

6. Verify that audit tables are moved.

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

TABLE_NAME TABLESPACE_NAME
----------- ---------------
FGA_LOG$ AUDIT_TBS
AUD$ AUDIT_TBS

Advertisements