Enable Auditing on database

Enabling auditing on database is simple but will require an outage to the instance and space under system tablespace.


SQL> connect / as sysdba
Connected.

SQL> alter system set audit_trail=DB scope=spfile ;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1337720 bytes
Variable Size             390071944 bytes
Database Buffers          138412032 bytes
Redo Buffers                5840896 bytes
Database mounted.
Database opened.
SQL>
SQL> connect dummy/dummy
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL>
SQL> conn / as sysdba
Connected.
SQL>

SQL> col OS_USERNAME for a15
SQL> col userhost for a30
SQL>
SQL> select os_username,
  2  username,
  3  userhost,
  4  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
  5  returncode
  6  from dba_audit_session
  7  where action_name = 'LOGON'
  8  and returncode > 0
  9  order by TIMESTAMP desc;


OS_USERNAME     USERNAME                       USERHOST                       TIMESTAMP           RETURNCODE
--------------- ------------------------------ ------------------------------ ------------------- ----------
oracle          DUMMY                          HostName                       07/09/2012 12:29:56       1017
 
The size of the audit table can be managed, we can purge the old logs as below:


 SQL> truncate table dba_audit_session;
truncate table dba_audit_session
               *
ERROR at line 1:
ORA-01702: a view is not appropriate here


SQL> select count(*) from SYS.AUD$;

  COUNT(*)
----------
    890947

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
    890827

SQL> truncate table SYS.AUD$;

Table truncated.

SQL> select count(*) from dba_audit_session;

  COUNT(*)
----------
         0

SQL>