Enabling Database Auditing 
Author: James L. Colestock

Step 1: Edit the Parameter File or SPFILE 

Create or change the audit_trail variable to DB, this will store audit results 
in the database as opposed to the file system. As of 9i this parameter change still requires 
a bounce of the database

Example:

audit_trail = DB 


 
Step 2:  Move the Audit Table 

Move the audit table out of the System tablespace 
Rename, drop and recreate the table and index, respectively,
by modifying and running the following script: 

connect "/ as sysdba" @move_audit.sql

move_audit.sql:

REM Name: move_audit.sql
REM -------------------------------------------------------------------------- 
REM REQUIREMENTS: 
REM    Should be run as SYS  
REM -------------------------------------------------------------------------- 
REM AUTHOR:  
REM    Scott Gossett  
REM MODIFIED:
REM    James Colestock 12-SEP-2002       
REM -------------------------------------------------------------------------- 
REM PURPOSE: 
REM    The purpose of this script is to move the existing SYS.AUD$ table 
REM    and its associated index I_AUD1 to a different tablespace. 
REM    This script creates a new tablespace AUD that will be used to 
REM    hold both objects.  
REM --------------------------------------------------------------------------- 
REM EXPLANATION: 
REM    Oracle stores audit trail records in the SYS.AUD$ base data dictionary 
REM    table.  The problem is this table grows inside the SYSTEM tablespace  
REM    and must have records deleted from it or be truncated, otherwise it 
REM    takes up all the room in the system tablespace.  This deleting and 
REM    truncating of the SYS.AUD$ table fragments the system tablespace. 
REM 
REM    The following script allows a DBA to move SYS.AUD$ out of the SYSTEM 
REM    tablespace.  By moving it out of system tablespace, control of the 
REM    table's size can be controlled without filling or fragmenting the  
REM    system tablespace. 
REM --------------------------------------------------------------------------- 
drop index SYS.I_AUD1
/
create table SYS.AUDX 
tablespace tmeda
as select * from sys.aud$ where 1 = 1 
/ 
rename SYS.AUD$ to SYS.AUD$$ 
/ 
rename SYS.AUDX to SYS.AUD$ 
/ 
create index I_AUD1
  on AUD$(sessionid, ses$tid)
    tablespace &1 
/
exit


 
Step 3: Audit what you wish using the appropriate 'audit' commands