Tuesday, May 22, 2018

How to move AUD$ table to new tablespace

Process to move the AUD$ table to a new tablespace.
SYSTEM is not ASSM managed, and will perform slower than ASSM tablespaces

1) ensure  free space in SYSAUX tablespace.. extend or add datafile to have space

2) alter table sys.aud$ enable row movement;

3)
set timing on
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
audit_trail_location_value => 'SYSAUX');
END;
/

4) verify object has moved to SYSAUX tablespace
select tablespace_name, segment_name,bytes/1024/1024 size_in_megabytes from dba_segments where segment_name in ('AUD$','FGA_LOG$');

No comments:

Post a Comment