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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.