Oracle Database Experts

If you are performing any kind of auditing on the database, we recommend that you move the aud$ tablespace from the system tablespace to another tablespace. In this example, we are moving the aud$ tablespace to a tablespace called SYSAUDIT_D.

If you are on ASM, you would change the file location to an ASM diskgroup such as ‘+data’.

def DB='&1'
def FS='&2'

CREATE TABLESPACE SYSAUDIT_D DATAFILE
  '/data/oracle/&DB/&FS/sysaudit_d_01.dbf' SIZE 200m AUTOEXTEND OFF
LOGGING ONLINE PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

create table audx tablespace SYSAUDIT_D as select * from aud$ where 1 = 2;
rename AUD$ to AUD$$;
rename audx to aud$;
drop index i_aud1;

create index i_aud1 on aud$(sessionid, ses$tid) tablespace SYSAUDIT_D;

set echo off

Posted by Charles Kim, Oracle ACE