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

From: Palooka <nobody_at_nowhere.com>
Date: Fri, 28 Aug 2009 20:26:54 +0100
Message-ID: <2cWlm.19074$156.18886_at_newsfe14.ams2>

On 08/28/2009 07:21 PM, ddf wrote:
> 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.
>
>

Audit session, not audit connect. And it works in 9i too. No need for triggers.

Palooka Received on Fri Aug 28 2009 - 14:26:54 CDT