About audits:
11g By default, the auditing option is enabled, and the default value of the Audit_trail parameter is DB, which indicates that the audit data will be recorded on the sys.aud$ Audit Dictionary base table in the database. In Oracle 10g, the default value of this parameter is none, which means that auditing is not enabled. Oracle claims that the audit logs enabled by default do not adversely affect the performance of most product databases, and Oracle recommends using an OS file-based audit logging method (OS audit trail files).
Because the Create session in 11g is logged as an audited permission, the system table space cannot be built due to disk space and will cause this audit record to fail, resulting in a normal user's new session not being created properly. Normal users will not be able to log in to the database. In this scenario, users can still create sessions with SYSDBA identities, delete a subset of records after the appropriate backup of the audit data, or truncateaud$ can resolve the problem.
When Audit_trail is set to OS, the audit log file is generated in the directory specified by the Audit_file_dest parameter. All of these files can be deleted or copied at any time.
The following permissions will be audited for all users:
sql> Select Privilege,success,failure fromdba_priv_audit_opts;
PRIVILEGE SUCCESS FAILURE
---------------------------------------- --------------------
CREATE EXTERNAL JOB by Access
CREATE any JOB by Access by Access
GRANT any OBJECT PRIVILEGE by Access
Exempt Access POLICY by Access
CREATE any LIBRARY by Access by Access
GRANT any PRIVILEGE by Access
DROP Profile by Access
ALTER Profile by Access
DROP any PROCEDURE by Access
ALTER any PROCEDURE by Access
CREATE any PROCEDURE by Access by Access
ALTER DATABASE by Access
GRANT any ROLE by Access by Access
CREATE public DATABASE LINK by Access
DROP any TABLE by Access by Access
ALTER any TABLE by Access by Access
CREATE any TABLE by Access by Access
DROP USER by Access
ALTER USER by Access
CREATE USER by Access by Access
CREATE SESSION By access by Access
AUDIT SYSTEM by Access
ALTER SYSTEM by Access
Rows selected.
existing audit records in the current database, Logno/logoff for Audit Create session produced by:
Sql> Select Action_name,count (*) from Dba_audit_trailgroup by Action_name;
Action_name COUNT (*)
---------------------------- ----------
LOGOFF by CLEANUP 40
LOGON 460
LOGOFF 377
ALTER USER 2
SYSTEM GRANT 12
ALTER SYSTEM 10
CREATE public synonym 5
ALTER DATABASE 2
CREATE DATABASE LINK 1
DROP public synonym 5
96.216 SYSTEM table space is too large:
96.216 the Logoff/logoff is more than 200 million records, the aud$ table size is 80G.
Workaround:
1. when the aud$ table is too large, you can clear the trial data from the table:
sql> truncate TABLE sys.aud$;
2. You can close the trial of the Create session:
Sql> Noaudit Create session;
3. to shut down the database for interrogation, restart the database:
Sql> alter system set Audit_trail=none Scope=spfile;
About Oracle 11g Audit files