Oracle Security

Purging Audit Information

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.

Figure 10-2. Data collection and summary cycle

Removing All the Data from SYS.AUD$

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 ...