Re: Tracking user sessions

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 30 Mar 2005 14:15:33 +0000 (UTC)
Message-ID: <d2eca5$rgk$1@titan.btinternet.com>

In the spfile (init.ora) set audit_trail = db to enable auditing.

From a privileged account issue:

    audit create session;

With this set up, Oracle will write a row into sys.aud$ as a session logs on, and update it with various statistics when the session logs off. The view dba_audit_session can than be queried on:

OS_USERNAME
USERNAME
USERHOST
TERMINAL
TIMESTAMP

ACTION_NAME
LOGOFF_TIME
LOGOFF_LREAD
LOGOFF_PREAD
LOGOFF_LWRITE
LOGOFF_DLOCK

SESSIONID
RETURNCODE
CLIENT_ID
SESSION_CPU TIMESTAMP is the logon time,
LOGOFF_TIME is self-explanatory.

Note - you may want to delete data from aud$ on a regular basis. Especially since it is created in the SYSTEM tablespace.

To disable the audit, issue:

    noaudit create session;

The overhead is relatively small - one insert at logon, one update at logoff. (Of course if you logon, execute one simple query and logoff, the overhead is a large fraction of the total work - you might want to view v$sysstat for the rate at which "logons cumulative" grows.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated March 9th 2005






"slobodan the 3rd" <recursive68_at_hotmail.com> wrote in message 
news:93c8244b.0503300519.2ee871ec_at_posting.google.com...


> Is there a way to track user sessions/usage over a period of

> weeeks/months. I'm looking to track when a user logins in and how long

> for, so I can generate stats on a particular users usage of the Oracle

> instance. The users are developers logging in using sqlplus typically.

> As far as I am aware there isn't a view that provides such info., is

> there a specific package that would give this sort of functionality ?

>

> TIA 

Received on Wed Mar 30 2005 - 08:15:33 CST