Re: On 9i and 10g, When an Oracle User Last Accessed the Database?

From: ddf <oratune_at_msn.com>
Date: Fri, 28 Aug 2009 11:21:05 -0700 (PDT)
Message-ID: <c79b0886-b6c0-4ca8-b6f8-3287892de0c0_at_c34g2000yqi.googlegroups.com>

On Aug 28, 11:10 am, "if_inves..._at_yahoo.com" <basis_consult..._at_hotmail.com> wrote:
> Hi,
>
> On Oracle 10g and 9i, is there a way to find out when a given user
> last accessed the
> database?
>
> Thanks,
> QZ

In '10g' this is fairly easy; in the init.ora file set

audit_trail=db

then restart the database. While still connected as SYS type:

audit connect;

and you're done as userid and logoff$time is populated in SYS.AUD$ (along with other bits of information). To report the last logoff for a user:

select userid, max(nvl(logoff$time, sysdate)) last_logoff from sys.aud$
group by userid;

All users who have connected and disconnected or who are still connected will be reported (those still connected will display a last_logoff value of sysdate).

With '9i' you'll need two triggers and a table to do this; read here:

http://oratips-ddf.blogspot.com/2006/05/audit-this.html

to see a script to create a table, logon and logoff triggers to provide that information.

David Fitzjarrell Received on Fri Aug 28 2009 - 13:21:05 CDT