The Oracle (tm) Users' Co-Operative FAQ

How can I maintain a history of user logins?

Author's name: Jonathan Lewis

Author's Email:

Date written: 15th April 2004

Oracle version(s): 8.1 – 10.1

Is there a quick easy way of finding out who’s been logging on to the database and how much work they’ve done?

Back to index of questions

If you want to get some idea of which users spend most time and consume most resources on the system, you don’t necessarily have to do anything subtle and devious to find out what’s been happening. There has been a (simple) audit trail built into the database for as long as I can remember. (The 8.1 – 10.1 in the banner simply covers the fact that I’ve only recently checked the following comments against those versions)

The init.ora file contains an audit_trail parameter. This can take the values true, false, none, os, db (the true/false options are for backwards compatibility). If you set the value to db (or true), then you have enabled auditing in the database. Once you have restarted the database (the parameter is not modifiable online), you can decide what events you want to audit.

For a quick cheap audit of connections, connect as a suitably privileged account (which typically means as a DBA), and issue the command:

    audit create session;

If you need to turn this audit off, the corresponding command is:

    noaudit create session;

The older syntax for the same level of audit is:

    audit connect;
    noaudit connect;

With this level of audit turned on, every session that logs on (except the SYS sessions) will insert a row into the table sys.aud$ giving various details of who they are and what time they connected. When the session ends, its last action is to update this row with various session-related details, such as log-off time, and the amount of work done. To make the results more readable, Oracle has superimposed the view dba_audit_session on top of the aud$ table; the 9.2 version of this view is as follows:

 Name                    Null?    Type

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

 OS_USERNAME                      VARCHAR2(255)       Who

 USERNAME                         VARCHAR2(30)

 USERHOST                         VARCHAR2(128)       Where

 TERMINAL                         VARCHAR2(255)

 TIMESTAMP               NOT NULL DATE                logon date/time

 ACTION_NAME                      VARCHAR2(27)       

 LOGOFF_TIME                      DATE                log off date/time

 LOGOFF_LREAD                     NUMBER              v$sess_io.consistent_gets

 LOGOFF_PREAD                     NUMBER              v$sess_io.physical_reads

 LOGOFF_LWRITE                    NUMBER              v$sess_io.block_changes

 LOGOFF_DLOCK                     VARCHAR2(40)        Number of deadlocks



 CLIENT_ID                        VARCHAR2(64)

 SESSION_CPU                      NUMBER              Session statistic. CPU used by this session

As you can see, there is quite a lot of helpful information here – perhaps good enough for most monitoring purposes. It is also a very light-weight tool, as it requires just one insert on logon, and an index access to update one row on log off.

There are a couple of administrative points. The aud$ table is in the system tablespace and is the one table in the sys schema that you are told you can delete data from. You may want to run a regular purge job to delete data that is more then N days old from this table.

You might consider moving this table to a separate tablespace – but there have been reports of problems with media recovery if you do this (possibly because the recovering processes tries to insert its own audit records and can’t because the tablespace needs recovery) and it is not supported by Oracle.

Finally, if you are running a physical standby database and open it in read only mode, you may find that you can’t connect to it as anyone other than sys. Auditing requires an insert/upate on aud$ - so can’t be allowed to on a read only database. You will have to remember to change the audit_trail parameter to none on your standby before you start it up.

Further reading: SQL Reference Manual – AUDIT and NOAUDIT commands.

Back to top

Back to index of questions