Oracle Audit Control part 1 – location of the audit table

One of the oldest problems with the Auditing capabilities within Oracle is that the SYS.AUD$ table resides in the SYSTEM tablespace. Unless you are rigorous in ensuring that your audit records are routinely pruned to keep the table manageable, it can single-handedly make the SYSTEM tablespace enormous.

Historically, we used to move the table and its associated objects to a new tablespace ourselves. In Oracle 7 is was a drop and re-create. Later we performed an alter table … move; command, coupled with an alter index rebuild. However, some bits frequently got left behind doing this…

In Oracle 10, a new package appeared: DBMS_AUDIT_MGMT. The procedure SET_AUDIT_TRAIL_LOCATION allowed you to move the table to a new tablespace. It didn’t work properly. It didn’t move indexes or LOB segments, and shouldn’t be used. However, roll on Oracle 11 and the (obvious) bugs have been ironed-out.

First of all, moving the table (NOTE: If the table is big, this may take quite a while. Only do this at a period of low system activity to avoid potential locking issues at the start and end of the move):

BEGIN
 DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
 AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
 AUDIT_TRAIL_LOCATION_VALUE => 'SYSAUX');
END;
/

This works a treat in Oracle 11 and 12 for the standard audit trail, and for fine-grained auditing. It successfully moved every object associated with SYS.AUD$.

select owner,table_name,tablespace_name from dba_tables where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX

select owner,table_name,tablespace_name from dba_lobs where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX
SYS                            AUD$                           SYSAUX

select owner,table_name,tablespace_name from dba_indexes where table_name = 'AUD$'
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ --------------------------
SYS                            AUD$                           SYSAUX
SYS                            AUD$                           SYSAUX

WARNING! Oracle still say that AUD$ should be in the system tablespace for upgrades. I can’t find anything that supercedes that, despite moving the table now being supported by an official package that works.

 

 

About these ads

One Response to Oracle Audit Control part 1 – location of the audit table

  1. Pingback: Oracle Audit Control part 2 – purging records | Neil Chandler's DBA Blog

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 26 other followers

%d bloggers like this: