Wednesday, March 20, 2019

How to move Audit table from system to another designated tablespace?

We have seen the database is slow and we dont know what. When we looked the audit and FRA table are in system table space and it is not recommended. Move table Aud$ from System to a new table space.

a-Run the below create tablespace

CREATE BIGFILE TABLESPACE AUDIT_DATA
DATAFILE '+DATA' SIZE 128G AUTOEXTEND ON NEXT 2G MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
LOGGING
ONLINE
SEGMENT SPACE MANAGEMENT AUTO;

b-Now move the tables to newly created table space from SYSTEM.

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

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_DATA');
END;
/

c-Verify the move was successful to AUDIT_DATA.

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


You should see improvement in performance. 

No comments:

Post a Comment