Hi all, I want to write a sql proc 1

[shantanu125]

Hi all,
I want to write a sql procedure that will create a log file which stores login sessions of users.I am using Oracle8i.
The application is in Power Builder (Client-server).This is necessary for tracking how many users generally connect at a time. I am unable to find any way...
Can anyone help!!!!!!!!!!!!!!
Shantanu

 

[spenglerr]

1) You will have to have DBA privileges
2) You will find your wishes in V_$SESSION_CONNECT_INFO
3) You will have to request this information regularily
4) You will build s.th. that is already delivered with your database client (OEM)
5) You will reinvent a history that you will also get with
Tools from QUEST
PRECISE
EMBARCADERO

 

[shantanu125]

Hi
thanx for the tips. I am able to find the solution and will post it soon.
shantanu

 

[Bimalaggarwal]

You can use the a select like this in your procedure :
-----------------------
select decode(s.audsid, userenv('sessionid'), '>') x
,rpad(s.sid||','||s.serial#,10) sid_serial
,rpad(s.process,11) pid_client
,rpad(p.spid,11) pid_session
,decode(s.status ,'ACTIVE','+' ,'INACTIVE',' ' ,'KILLED','-' ,'?') status
,rpad(s.client_info,11) client_info
,rpad(s.username,8) username
,rpad(s.osuser,13) osuser
,rpad(s.machine,20) machine
,nvl(s.program,p.program) program
,rpad(decode(a.name,'UNKNOWN',null,a.name),19) command_in_progress
,rpad(to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS'),20) logon
--
from v$session s
,v$process p
,v$lock l
,sys.audit_actions a
--
where p.addr (+)= s.paddr
and a.action (+)= s.command
and l.sid (+)= s.sid
and l.type (+)= 'JQ'
--
and decode(s.status ,'ACTIVE','+' ,'INACTIVE',' ' ,'KILLED','-' ,'?') like '&1%'
and ',,'||nvl(s.username,' ')||',' like upper('%,&2,%')
and ',,'||p.spid||',' like ('%,&3,%')
--
--and audsid >0
--
order by decode(s.username ,'K','1' ,'I','2' ,s.username)
,s.logon_time desc
;