Does anyone know how can we determine the connection of a session? Using database event triggers or any views :-?
Printable View
Does anyone know how can we determine the connection of a session? Using database event triggers or any views :-?
Look in v$session
Code: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
nono i mean the total connection time
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
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
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]
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
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 :p
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]
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
ahh no need found this on metalink
2. Is audsid a unique key?
Answer
------
AUDSID is unique in case of user sessions. When AUDSID is assigned to a new
session the SYS.AUDSES$ sequence is incremented using NEXTVAL and the new
value is assigned to the session.
In case of sessions connecting as 'internal' audsid is 0, SYS.AUDSES$
sequence is not incremented.
in my case noone would use svrmgrl so guess these triggers would work ok :D