Re: On 9i and 10g, When an Oracle User Last Accessed the Database?
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