how long has the session been connected

[pando]

Does anyone know how can we determine the connection of a session? Using database event triggers or any views :-?

[jdorlon]

Look in v$session

[marist89]

SQL> l
  1  select username, logon_time from v$session
  2* where username is not null
SQL> /

USERNAME                       LOGON_TIME
------------------------------ -------------------
SYS                            2001/06/25 14:07:07
SYSTEM                         2001/06/25 14:07:16
DBSNMP                         2001/06/25 14:07:19

[pando]

nono i mean the total connection time

[jdorlon]

Hi Pando,

What do you mean? Will sysdate - logon_time do it?

86400 * (to_number(sysdate) - to_number(logon_time))

will give you the amount of time in seconds that a session
has been connected to the database.

-John

[rsuri]

well, shouldn't that be the difference between the logon_time and the current_time (sysdate) ?

May be we are missing the point altogether ???

- Rajeev

[pando]

Hi

We want to store in a table the connection time of each session.
I have created a log_info table with these columns

logon_time
username
osuser
program
audit_id
logoff_time

I have created two triggers, one after logon and one for before logoff. I have used audsid for this purpose, the trigger is smth like (I dont have the codes here)


create or replace trigger logon_trg after logon on database
declare
session_rec v$session%rowtype;
begin

select *
into session_rec
from v$session
where audsid=userenv('sessionid');

if
session_rec.program = 'SQLPLUSW.EXE'
then
insert into lsc.log_info
values(sysdate, user, session_rec.osuser, session_rec.program, userenv('sessionid'), null);
end if;
end;
/

create or replace trigger logoff_trg before logoff on database
declare
session_rec v$session%rowtype;
begin

select *
into session_rec
from v$session
where audsid=userenv('sessionid');

if
session_rec.program = 'SQLPLUSW.EXE'
then
update lsc.log_info
set logoff_time=sysdate /*here I could use sysdate-logon_time*/
where audit_id=userenv('sessionid');
end if;
end;
/


I have tried this and it works however I am trying with 3 or 4 sessions only, since this table is not being truncated after long time obviously my audit_id will be duplicated (I am using it here as a candidate key), I need to know if there is another way to obtain the connection of a session

Cheers

[Edited by pando on 06-25-2001 at 04:50 PM]

[rsuri]

Hi Pando,
The session_id should be unique for each session until the database is recycled. So what you may want to do is create a Sequence and increment it whenever the database is STARTUP. And then use the combination of that Sequence and Session_id as the PK for your log_info table.
What do you think ?

- Rajeev

[pando]

Hi

I have thoght about using sequence as well but I dont know how you can make the trigger to *memorize* the sequence of each session when it comes to the update part and update the correct row!

I am not very good in programming... maybe my logic is wrong

Also I have noticed that if I sqlplus crashes the update trigger doenst work

[Edited by pando on 06-25-2001 at 04:51 PM]

[rsuri]

Well what you need to do is as following --

1. Create a Sequence (let's say log_seq)
2. Create a database Startup Trigger that will increment the sequence whenever the database is brought up --

create or replace trigger t1 after startup on database
...code to increment the sequence...something like ...SELECT log_seq.NextVal INTO :x from DUAL;

3. In your Update trigger (logon_trg and logof_trg) you need to use the log_Seq.CurrVal to insert/compare the current value of the Sequence.

- Rajeev