SYS. AUD $ unable to resize, causing logon Failure

Yesterday, my colleague said that a test database could not be logged on, with PLSQLDeveloper login prompt: ERROR: ORA-00604: erroccurredatrecursivesqllevel1or

Yesterday, my colleague said that a test database could not be logged on, with PLSQL Developer login after the prompt: ERROR: ORA-00604: error occurred at recursive SQL level 1OR

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 1ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEMORA-02002: error while writing to audit trailORA-00604: error occurred at recursive SQL level 1ORA-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. Audit is enabled for 11 GB by default.

SQL> show parameter audit_trailNAME                                 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 should 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. A method is also introduced in MOS: delete from sys. aud $ WHERE obj $ name ='

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.