As we’ve mentioned several times in this chapter, you need to closely monitor the SYS.AUD$ table as long as any form of auditing is occurring in the database. This is the only data dictionary table from which Oracle permits DBAs to delete information. The wise DBA will archive the information from this table before removing the data. One way to archive data is to create a summary table and move the information of interest into the summary table before removing the data from the SYS.AUD$ table. Since the auditing views rely on information from the SYS.AUD$ table, remember that when you remove data from this table, the data will disappear from the audit views as well. Another way to archive the data from this table is to create a copy of the table in another schema and then export that schema. Figure 10.2 shows a possible purge cycle.
You can remove all the data from the SYS.AUD$ table by issuing the statement:
TRUNCATE TABLE sys.aud$;
or alternatively:
DELETE FROM sys.aud$;
Why would you use DELETE rather than TRUNCATE? As rows of information are inserted into a table, Oracle uses a mechanism called the “high-water mark” to indicate the location of the last row of data in the table. This mark tells Oracle how many blocks to examine during a full table scan. When you issue a DELETE statement, Oracle marks the row or rows you have specified as deleted but does not actually erase the rows from the table. From a performance perspective, if you merely delete all the rows from the audit table, you will not reset the high-water mark for the table. If a full table scan is required to obtain data from the table, performance might slow down substantially as Oracle examines every block of the table — even the “empty” blocks ( those blocks containing rows marked as deleted).
If you issue the TRUNCATE command, on the other hand, the high-water mark is reset to the first block in the table and performance will be improved.
If you want to retain specific data in the table but want to ensure that the table is kept to a manageable size, you can create a script to delete only selected rows from the SYS.AUD$ table. You might base the deletions on a specific range of dates or by a specific group of actions or users.
As we described in the earlier “Creating a summary table” section, you may want to retain the information from the SYS.AUD$ table into a summary table before you delete any rows.