SYS. AUD $ unable to resize, causing logon Failure

Yesterday, my colleague said that a test database could not be logged on. After logging on with PLSQL Developer, the following message is displayed:

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS. AUD $ by 8192 in tablespace SYSTEM
A ORA-02002: error while writing to audit trail
ORA-00604: error occurred at recursive SQL level 1
ORA-01653: unable to extend table SYS. AUD $ by 8192 in tablespace SYSTEM

It seems that @ dbsnake has said this before.

First, SYS. AUD $ is a data dictionary table, which is stored in the SYSTEM tablespace. Audit information is stored, and audit is enabled for 11 GB by default.

SQL> show parameter audit_trail
NAME TYPE VALUE
-----------------------------------------------------------------------------
Audit_trail string DB

The preceding prompt clearly indicates that the SYS. AUD $ table in the SYSTEM tablespace cannot be expanded. It may be due to insufficient disk space or insufficient data file space allocated in the SYSTEM tablespace. There are two ways to solve the problem above:

1. Expand the SYSTEM tablespace.

2. Reduce the capacity of SYS. AUD $.

The second method is available. The following conclusions can be drawn from the article MOS: 73408.1:

SYS. AUD $ is the only SYS object that shocould ever be directly modified

SYS. AUD $ is the only SYS object that can be directly modified.

How can we reduce the capacity of SYS. AUD $?

1. The simplest method is truncate table SYS. AUD $, which is the most direct, provided that audit information is not retained.

2. MOS also introduces a method: delete from sys. aud $ WHERE obj $ name = '<object_name>';, DELETE the audit information of the specified object in SYS. AUD $. The problem is obvious, that is, it will not reduce the high water level line.

3. You can also back up the audit table and then reduce the capacity.

Create table backup_aud $ as select * from sys. aud $;

Truncate table aud $;

Exp file = aud_backup.dmp tables = backup_aud $

Drop table backup_aud $;

If you only need to reduce the space occupied by SYS. AUD $, run insert into aud $ select * from backup_aud $ again to import the data.

4. Delete SYS. the AUD $ TABLE requires the corresponding permissions: either SYS user, delete any table system permission, or SYS user sets SYS. the DELETE permission of the AUD $ table is granted to this user. You can also use the delete_catalog_role role. However, in versions earlier than 11.2.0.2, a bug may cause this role to have no permission to delete SYS. AUD $. You need to manually add it. For details, see the bug description of 9697811.

5. Introduction to MOS: The delete any table privilege only applies to SYS objects if O7_DICTIONARY_ACCESSIBILITY = TRUE indicates that if The O7_DICTIONARY_ACCESSIBILITY parameter is TRUE, only SYS users can have The delete any table permission. I checked some information and said that O7_DICTIONARY_ACCESSIBILITY is used to protect the data dictionary base table. If it is set to FALSE, SYS cannot Log On As A Common User role and must be logged on as sysdba, if the value is TRUE, normal users can also access the data dictionary base table, which may cause security problems.