Re: whos logged in when

This is what I'm using, and it works just fine under 8.1.6. It's the first trigger I've ever written, so I don't know if it's terribly elegant, but it works:

TRIGGER bef_logoff_info before logoff on database declare

    osusr varchar2(30);
    machine varchar2(64);
    program varchar2(64);
    logon date;
begin
  select osuser, machine, program, logon_time     into
   osusr,
   machine,
   program,
   logon
    from v$session
   where audsid = (select userenv('SESSIONID') from dual);   insert into log_info values (user, osusr, machine, program, logon, sysdate);   commit;
end;

robin

Ian Ledzion wrote:

> The problem with this is that the logon trigger is outside the session, so
> does not recognise the user name. We tried this at an Oracle Trigger course
> (8.1.5 on Sun) and it just inserts blank values.
>
> "ok" <j_p_x_at_hotmail.com> wrote in message
> news:tfglqlg2329q86_at_corp.supernews.com...
> > Another way for 8i (as you said), create a table and write a logon and
> > logoff (database) trigger to insert a record into this table.
> >
> > Such as:
> >
> > create table logonf_log (username varchar2(10), log_date date, on_off
> > varchar2(3)) tablespace users;
> >
> > create or replace trigger logon_aud
> > after logon on database
> > begin
> > if login_user not in ('SYS','OEM', 'DBSNMP' 'SYSTEM')
> > then
> > insert into logonf_log values (login_user, sysdate, 'ON');
> > else
> > null;
> > end if;
> > end;
> > /
> >
> > You can figure out the logoff trigger. Then you can show the records in
> > logonf_log table to your boss. Good luck.
> > ______________
> > OK
> >
> >
> > Sybrand Bakker wrote:
> > >
> > >
> > >
> > > "Simon Cunningham" <cs40_at_gre.ac.uk> wrote in message
> > > news:3AF7C827.C36E0D3E_at_gre.ac.uk...
> > > > Is there a way to find out how much the database is being accessed by
> > > > its users??
> > > > I can think of having a or a trigger quering the v$session view but Im
> > > > not sure you can do that. Other than that could you have a script
> > > > running on the server(NT 4 with a 8i db)???
> > > >
> > > > I have to show the bosses how busy our db's are accessed
> > > >
> > > > Thanks in advance
> > > >
> > >
> > > Fortunately you can't have triggers on any v$ object.
> > > Few ideas:
> > > run utlbstat and utlestat, utlestat is providing a report on how many
 users
> > > are logged in.
> > > Of course you can run a script on v$session
> > > select count(distinct schemaname)
> > > from v$session
> > > where type = 'USER'
> > > you can also enable audit on your database (change audit_trail in
 init.ora
> > > to true and bounce the database, followed by audit connect whenever
> > > successful.
> > > You now have audit records in dba_audit_session.
> > > You can also write an on schema trigger, which inserts a record in your
 own
> > > table when the user logs in.
> > > Just a few ideas...
> > >
> > > Regards,
> > > Sybrand Bakker, Oracle DBA
> > >
> > >
> > >
> >
> >
> > --
> > Posted via CNET Help.com
> > http://www.help.com/
  Received on Wed May 09 2001 - 12:48:27 CDT