What happens if sys.aud$ is full in Oracle?

Question

I am not seeing this in the documentation, but what happens when the Oracle 10.2 audit trail is full? Does it just not making any more audit records? Overwrites the oldest records? Shuts down the database? And is there any way I can change that?

Yes, I know 10.2 is not supported, but I don't have control over that. And yes there are procedures in place that should never need me to find out first-hand. But in cases of emergency failure of the auditing operations, what is the default behavior if the audit trail becomes full?

Answer 1

To further expand on my comment, the sys.aud$ table is stored, by default, in the SYSTEM tablespace. When the SYSTEM tablespace fills, your database halts.

There are a few options available to you to manage this, but these will depend on what version of 10g you have installed. As of the 10.2.0.5 patch, a new package, DBMS_AUDIT_MGMT was included that allows you to move the sys.aud$ table to a user Tablespace. If you are at this patch-level, move the audit table, plain-and-simple. Filling a user tablespace makes write operations against said user tablespace halt, but it won't bring everything else down like the SYSTEM tablespace filling will. This functionality was backported, but not to all 10.2 versions. You can check Doc ID 731908.1 on support.oracle.com to get all the details.

If you don't have the ability to upgrade to 10.2.0.5, you will need to regularly purge the sys.aud$ table by some other means so that it doesn't continue to grow and consume space. If it gets too unruly and you find you do need to "shrink" the table, you will likely need to TRUNCATE it to release the white space back to the tablespace. I've tried shrinking this file using the typical trick of enabling row movement and shrinking the table, but because records are often appended to the end of the sys.aud$ table and are added regularly, it often doesn't shrink much, if at all, which is why a TRUNCATE operation is often the most effective method here to free up some space.

Finally, your last option is to just disable the Audit. If it's not needed, this is probably the easiest option available to you.

Answer 2

Your database essentially freezes and does not allow any new connections or transactions that would be writing into the audit logs. Your only bet would be to connect directly on the server, e.g. "sqlplus / as sysdba" and then add space (or truncate it if auditing is not important to that database).

For this reason we have scripts to backup aud$ and truncate it regularly. We don't want horrible freezing going on in production...

You also want to move aud$ away from SYSTEM, something like this:

alter table sys.aud$ move tablespace aud_data;
alter table sys.aud$ move lob (sqlbind) store as (tablespace aud_data);
alter table sys.aud$ move lob (sqltext) store as (tablespace aud_data);
alter index sys.I_AUD1 rebuild tablespace aud_idx;