Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: ON-LOGON Trigger ??

Re: ON-LOGON Trigger ??

From: <fitzjarrell_at_cox.net>
Date: 28 Jul 2005 21:04:29 -0700
Message-ID: <1122609869.598744.229510@g14g2000cwa.googlegroups.com>

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,

   sysdate,
   to_char(sysdate, 'hh24:mi:ss'),
   null,
   null,
   null
);
END;
/

create or replace trigger

   logoff_audit_trigger
BEFORE LOGOFF ON DATABASE
BEGIN

-- ***************************************************
-- Update the last action accessed
-- ***************************************************
update
stats$user_log
set
last_action = (select action from v$session where sys_context('USERENV','SESSIONID') = audsid) where
sys_context('USERENV','SESSIONID') = session_id;
--***************************************************
-- Update the last program accessed
-- ***************************************************
update
stats$user_log
set
last_program = (select program from v$session where sys_context('USERENV','SESSIONID') = audsid) where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- Update the last module accessed
-- ***************************************************
update
stats$user_log
set
last_module = (select module from v$session where sys_context('USERENV','SESSIONID') = audsid) where
sys_context('USERENV','SESSIONID') = session_id;
-- ***************************************************
-- 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
stats$user_log
set
elapsed_minutes =
round((logoff_day - logon_day)*1440)
where
sys_context('USERENV','SESSIONID') = session_id; END;
/

David Fitzjarrell Received on Thu Jul 28 2005 - 23:04:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US