Maintenance of audit trail (sys.aud$)

Hello all

For a number of reasons we have historically had "audit select table by session" enabled. This worked very well until 11.2 (we are at 11.2.0.2), when this means that every time a user touches a table, an audit record is inserted into sys.aud$ This means lots and lots of inserts.

We used to manage the audit trail by moving the data into another table regularly (monthly, by insert .... select from sys.aud$ where n#timestamp < x; delete from sys.aud$ where n#timestamp < x. However, on 11.2 we changed that to insert ...; truncate sys.aud$ whenever the database restarts (weekly). The truncate moves the highwater mark on aud$, which works wonders on insert performance (much less io than if insert is into earlier used blocks below hwm).