ORA-02002

This was tested on Oracle 11.2.0.2.7. I wondered what would happen if you were auditing connections, SYS.AUD$ filled up then you tried to login again. First I started auditing: 

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> audit create session

  2  /

 

Audit succeeded.

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

-------------------------------------------------------

TRUE

 

SQL>

Then I checked that auditing was working: 

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         0

 

SQL> conn andrew/reid

Connected.

SQL> show user

USER is "ANDREW"

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         2

 

SQL>

Next I looked at the current and maximum possible size of SYS.AUD$: 

SQL> select count(*) from dba_extents

  2  where owner = 'SYS'

  3  and segment_name = 'AUD$'

  4  /

 

  COUNT(*)

----------

         1

 

SQL> select next_extent, max_extents

  2  from dba_tables

  3  where owner = 'SYS'

  4  and table_name = 'AUD$'

  5  /

 

NEXT_EXTENT MAX_EXTENTS

----------- -----------

    1048576  2147483645

 

SQL>

 

I tried to limit the maximum possible size of SYS.AUD$. I found that I could adjust the size of the next extent: 

SQL> alter table sys.aud$ storage (next 8k)

  2  /

 

Table altered.

 

SQL>

... but not the maximum number of extents: 

SQL> alter table sys.aud$ storage (maxextents 10)

  2  /

alter table sys.aud$ storage (maxextents 10)

*

ERROR at line 1:

ORA-25150: ALTERING of extent parameters not permitted

 

SQL>

So the only way to make SYS.AUD$ run out of space is to set a limit on the size of the tablespace it is in. To make this easier, I moved SYS.AUD$ to the USERS tablespace, which has 2 megabytes and autoextend off in this database: 

SQL> alter table sys.aud$ move tablespace users

  2  /

 

Table altered.

 

SQL>

Then I set up a loop in a UNIX shell script to connect to the database again and again. Each iteration connected as ANDREW first then as SYS. It started with a count of 1: 

$count is 1

 

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:37:44 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> USER is "ANDREW"

SQL> Connected.

SQL> USER is "SYS"

SQL>   2

AUDIT_ROWS

----------

         4

 

SQL>   2    3    4

LOGONS_CUMULATIVE

-----------------

             4505

 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Connection as user ANDREW failed with ORA-02002 when $count reached 1986: 

$count is 1986

 

SQL*Plus: Release 11.2.0.2.0 Production on Tue Oct 16 18:51:36 2012

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> USER is "ANDREW"

SQL> ERROR:

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 8 in tablespace USERS

 

Connected.

SQL> USER is "SYS"

SQL>   2

AUDIT_ROWS

----------

      3973

 

SQL>   2    3    4

LOGONS_CUMULATIVE

-----------------

             8484

 

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

I was still able to connect as SYS so, if I had wanted to continue using the database I could have deleted rows from SYS.AUD$, enlarged the USERS tablespace or simply stopped auditing. 

Reply to: Reply to Andrew Reid

Send

Introduction to Auditing

A database’s audit trail is stored in the SYS.AUD$ table:

SQL> desc sys.aud$

Name                       Null?    Type

-------------------------- -------- ------------------

SESSIONID                  NOT NULL NUMBER

ENTRYID                    NOT NULL NUMBER

STATEMENT                  NOT NULL NUMBER

TIMESTAMP#                 NOT NULL DATE

USERID                              VARCHAR2(30)

USERHOST                            VARCHAR2(128)

TERMINAL                            VARCHAR2(255)

ACTION#                    NOT NULL NUMBER

RETURNCODE                 NOT NULL NUMBER

OBJ$CREATOR                         VARCHAR2(30)

OBJ$NAME                            VARCHAR2(128)

AUTH$PRIVILEGES                     VARCHAR2(16)

AUTH$GRANTEE                        VARCHAR2(30)

NEW$OWNER                           VARCHAR2(30)

NEW$NAME                            VARCHAR2(128)

SES$ACTIONS                         VARCHAR2(19)

SES$TID                             NUMBER

LOGOFF$LREAD                        NUMBER

LOGOFF$PREAD                        NUMBER

LOGOFF$LWRITE                       NUMBER

LOGOFF$DEAD                         NUMBER

LOGOFF$TIME                         DATE

COMMENT$TEXT                        VARCHAR2(4000)

CLIENTID                            VARCHAR2(64)

SPARE1                              VARCHAR2(255)

SPARE2                              NUMBER

OBJ$LABEL                           RAW(255)

SES$LABEL                           RAW(255)

PRIV$USED                           NUMBER

SESSIONCPU                          NUMBER

 

SQL>

 

You can tell Oracle what to monitor using the AUDIT command. The example below will record when the SYSTEM user connects to the database:

 

SQL> audit create session by system;

 

Audit succeeded.

 

SQL>

 

This database currently has auditing turned off i.e the audit_trail initialisation parameter is set to NONE:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'audit_trail'

SQL> /

 

VALUE

------------------------------

NONE

 

SQL>

 

So subsequent logins by SYSTEM will not be recorded in SYS.AUD$:

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

 

In versions up to and including Oracle 11, you cannot turn auditing on while a database is open:

 

SQL> alter system set audit_trail = true;

alter system set audit_trail = true

                 *

ERROR at line 1:

ORA-02095: specified initialization parameter cannot

be modified

 

SQL>

 

You have to alter the pfile or spfile as appropriate and bounce the database. Once audit_trail is set to true, auditing will start working and a row will appear in SYS.AUD$ after SYSTEM has connected to the database:

 

SQL> select value from v$parameter

  2  where name = 'audit_trail'

  3  /

 

VALUE

------------------------------

TRUE

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

You can query records in SYS.AUD$ in the normal way. The TIMESTAMP# column has the logon time and a PRIV$USED of 5 stands for CREATE SESSION:

 

SQL> l

  1* select userid, timestamp#, priv$used from sys.aud$

SQL> /

 

USERID     TIMESTAMP#  PRIV$USED

---------- ---------- ----------

SYSTEM     21-MAR-12           5

 

SQL>

 

Various views record the auditing which you have asked Oracle to do in your database. The audit request we made above is stored in DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

USER_NAME            PRIVILEGE

-------------------- --------------------

SYSTEM               CREATE SESSION

 

SQL>

 

You can stop an audit request with the NOAUDIT command:

 

SQL> noaudit create session by system

  2  /

 

Noaudit succeeded.

 

SQL>

 

Once you have done this, the relevant entry disappears from DBA_PRIV_AUDIT_OPTS:

 

SQL> select user_name, privilege

  2  from dba_priv_audit_opts

  3  /

 

no rows selected

 

SQL>

 

And connections by SYSTEM will no longer be recorded in SYS.AUD$:

 

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL> conn system/manager

Connected.

SQL> select count(*) from sys.aud$

  2  /

 

  COUNT(*)

----------

         1

 

SQL>

 

Unlike other objects owned by SYS, you are allowed to delete rows from SYS.AUD$.

 

SQL> delete sys.aud$

  2  /

 

1 row deleted.

 

SQL>

 

However, you should check first that it does not contain rows produced by your colleagues with separate audit requests!