Issuing of moving SYS.AUD$ to another tablespace

Oracle stores audit trail records in the SYS.AUD$ base data dictionary table which resides in SYSTEM tablespace.

Based on the auditing option this SYS.AUD$ table grows out of order inside the SYSTEM tablespace and must have records deleted from it or be truncated, otherwise it will take up all the room in the SYSTEM tablespace. This deleting and truncating of the SYS.AUD$ table will fragment the SYSTEM tablespace.

So it may be one's choice to move the audit table out of the SYSTEM tablespace and then enable auditing without touching SYSTEM tablespace.

Until 11g the way to move out SYS.AUD$ is not supported. If you want to do it you have to do it manually which is discussed in How to move audit table out of SYSTEM tablespace which is not supported. 'Not supported' means Oracle Support cannot involve development if problems arise because of the triggers you put on the AUD$ table.

In 11g you can do it by the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION provided with the DBMS_MGMT package.
Related Documents
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html

How to move audit table out of SYSTEM tablespace

Oracle strongly recommended to use the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION
provided with the DBMS_MGMT package in order to move audit trail table out of SYSTEM tablespace. But the procedure SET_AUDIT_TRAIL_LOCATION by default is not available until 11g. It is available in 11g.

However with manual script you can move SYS.AUD$ table out of SYSTEM tablespace. But you need to remember moving AUD$ out of SYSTEM tablespace is not a
supported procedure. Oracle does not support changing ownership of AUD$, or any
triggers on it.

Below is the scripts that you can do as your own risk,
Step 01: Connect to database as SYS user.
conn / as sysdba

Step 02: Create tablespace where audit file will reside.
create tablespace AUDIT_TBS
datafile '/oradata2/datafile/aud01.dbf' size 10M autoextend on maxsize unlimited;

Step 03: Create audit table inside AUDIT_TBS
create table aud_aux tablespace AUDIT_TBS
as select * from aud$ where 1 = 2;
Note that no rows will be created in this state.

Step 04: Rename the original Audit table.
rename AUD$ to AUD$$;

Step 05: Rename the aud_aux to AUD$
rename aud_aux to aud$;

Step 06: Create Index on the AUD$ table.
create index aud_i
on aud$(sessionid, ses$tid)
tablespace AUDIT_TBS;

Related Documents
How to Reorganize Audit trail SYS.AUD$ Table
How to truncate or delete rows from audit trail table sys.aud$
About Audit_trail Parameter

How to Reorganize Audit trail SYS.AUD$ Table

You may want to reorganize your auditing table if you optionally delete records from it regularly. In the following steps it is described.
1)Enable restricted session.
In order to ensure consistency in the auditing table temporary disable auditing activity. You can do this by opening database with STARTUP RESTRICT or during open make the system as restricted session.

SQL> connect / as sysdba
Connected.

SQL> alter system enable restricted session;
System altered.

Check if sessions are still connected by,
SQL> select sid, serial#, username from v$session;

If necessary kill these sessions with,
SQL> alter system kill session 'sid , serial#';

2)Copy SYS.AUD$ table.
SQL>CREATE TABLE audit_record TABLESPACE USERS as select * from SYS.AUD$;
You now can take a dump of audit_record.

3)Truncate SYS.AUD$ table.
SQL> truncate table sys.aud$;
Table truncated.

SQL> select count(*) from SYS.AUD$;
COUNT(*)
--------
0

4)Copy the rows back to SYS.AUD$.
SQL> insert into sys.aud$ select * from audit_record;

You can also import it if you exported it in step 2.

5)Drop the audit_record table(optional).
SQL>DROP TABLE audit_record;

Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html

How to truncate or delete rows from audit trail table sys.aud$

1)Only appropriate privileged user can do delete operation on SYS.AUD$ table. The user must have either of the following privileges.
-SYS user.
-DELETE ANY TABLE system privilege. (If O7_DICTIONARY_ACCESSIBILITY=TRUE)
-A user to whom SYS has granted the object privilege DELETE on SYS.AUD$ table.

2)Before deleting any rows you may want to archive the table. You can achive this by creating a table from SYS.AUD$ and export that. Don't export SYS.AUD$ directly.
SQL>CREATE TABLE AUDIT_RECORD TABLESPACE users as select * from SYS.AUD$;
Now export the table as,
SQL> host exp tables=AUDIT_RECORD file=audit_record.dmp

3)To delete all records from audit trail table SYS.AUD$ issue,
SQL>DELETE FROM SYS.AUD$;

To delete all records of particular audited table from the audit trail issue,
SQL>DELETE FROM sys.aud$ WHERE obj$name='&table_nmae';

But deleting in this way will not reduce size on the system tablespace or aud$ table. In order to reduce size follow section 4.

4)Truncate audit table to reduce size.
SQL>CONN / as sysdba
SQL>TRUNCATE TABLE sys.aud$;

Truncate uses the DROP STORAGE clause but keeps only minextents extents, thus only 1 extent.
Related Documents
http://arjudba.blogspot.com/2008/10/issuing-of-moving-sysaud-to-another.html
http://arjudba.blogspot.com/2008/10/how-to-move-audit-table-out-of-system.html
http://arjudba.blogspot.com/2008/10/how-to-reorganize-audit-trail-sysaud.html
http://arjudba.blogspot.com/2008/05/about-audittrail-parameter.html
http://arjudba.blogspot.com/2008/04/configure-and-administer-database.html
http://arjudba.blogspot.com/2008/04/basics-of-database-auditing.html