Re: ** Auditing For Logon Time & Logoff Time ?

Craig Harper <caharper_at_hooked.net> wrote:
>I am wondering if it is possible to use auditing - session, to capture
>when a user logged on and logged off?
>
>What are the performance issuses if any?
>
>Background:
>
>I have users that connect via FORMS 4.5.6 and SQL*NET through a modem
>connection. I was going to (infact I did) create a connect_history table
>and have forms log the users connect time and date etc when the form
>started. Then I realized that if the user diconected their modem without
>exiting the program correctly that I would not be able to get their
>logoff time.
>
>Now I am looking for a way to track logon time, logoff time,user, and if
>the session is idle wheather that is due to the user not doing anything
>or the modem connection having been broken?

I use database session auditing to accomplish most of this. When a connection is made, an entry is placed in the SYS.AUD$ table showing the logon time. When the connection closes, different columns in this same row are updated showing logout time, logical reads and writes, and physical reads for the session. If the session is closed non-gracefully (client crashes, modem hangs up, etc.) the row is updated after the DB session is cleaned up, and the action column in the audit table show "LOGOFF BY CLEANUP" instead of "LOGOFF". All the other columns are still correct. I wrote a small form for displaying this stuff, with calculations for session duration and hit ratio. The actual dictionary view to use is DBA_AUDIT_SESSION, which shows a subset of the SYS.AUD$ table.

The performance impact is minor, as only one row gets inserted at connect time and updated at disconnect time. The only time you wouldn't get the disconnect time for a session is if the database server crashed while the user was still connected.

Hope this helps.

Roger Harris Received on Fri Dec 08 1995 - 00:00:00 CET