Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: ON-LOGON Trigger ??
Robert Bralic wrote:
> <fitzjarrell_at_cox.net> wrote in message
> news:1122468689.416115.169070_at_z14g2000cwz.googlegroups.com...
> >
> >
> > Robert Bralic wrote:
> > > Hallo,
> > >
> > > I would likre to write in PL/SQL small tool for
> > > dba-s,that will note from "V$SESSION" for any,
> > > login username and other datas into a table,
> > > something like "last" on UNIX, but I don't know
> > > how to write and compile this function with that
> > > trigger(ON-LOGIN, if this exysts), so I need
> > > a little help.
> > >
> > >
> > > Thanks in advance, Robert !
> > > robert.bralic_at_si.t-com.hr
> >
> >
> > It has been done before, and such code is available in "Oracle9i:
> > PL/SQL Programming", by Scott Urman. You can download the code he's
> > written here:
> >
> > http://shop.osborne.com/cgi-bin/oraclepress/downloads.html
> >
> > LogPkg1.sql and LogPkg2.sql are what you need to use. Stop trying to
> > re-invent the wheel.
> >
> >
> > David Fitzjarrell
> >
>
> Dear,
>
> I'am sory for my question but internet is sometimes to big,
> and in my company they don't by me books, and I 'am not
> well payed.Somethimes when I have a free time is to boring,
> and I make of some small, and usable software just for kill a boringes...
>
> Thanks, Robert..!!
Here is the script I use:
create table
stats$user_log
(
user_id varchar2(30), session_id number(8), host varchar2(30), last_program varchar2(48), last_action varchar2(32), last_module varchar2(32), logon_day date, logon_time varchar2(10), logoff_day date, logoff_time varchar2(10), elapsed_minutes number(8)
create or replace trigger
logon_audit_trigger
AFTER LOGON ON DATABASE
BEGIN
insert into stats$user_log values(
user,
sys_context('USERENV','SESSIONID'),
sys_context('USERENV','HOST'),
null, null, null,
create or replace trigger
logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN
-- ***************************************************-- Update the last action accessed
-- ***************************************************update
--***************************************************-- Update the last program accessed
-- ***************************************************update
-- ***************************************************-- Update the last module accessed
-- ***************************************************update
-- ***************************************************-- Update the logoff day
-- ***************************************************update
stats$user_log
set
logoff_day = sysdate
where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************-- Update the logoff time
-- ***************************************************update
stats$user_log
set
logoff_time = to_char(sysdate, 'hh24:mi:ss') where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************-- Compute the elapsed minutes
-- ***************************************************update
David Fitzjarrell Received on Thu Jul 28 2005 - 23:04:29 CDT