和某个session关联的进程和用户信息

今天终于把session的sid, db server操作系统进程，application server操作系统进程，以及相关用户信息全部联系起来。[@more@]

select /*+ ORDERED */
chr(39)||s.sid||','||s.serial#||chr(39) sid_serial
, to_char(s.logon_time,'yyyymmdd HH24:MI:SS') logon
, floor(last_call_et/3600)||':'||
floor(mod(last_call_et,3600)/60)||':'||
mod(mod(last_call_et,3600),60) IDLE
, s.username o_user
, s.osuser os_user
, s.status status
, u.user_name apps_user
--, n.TERMINAL_ID terminal
, s.process midTier_process
, p.spid dbTier_process
, p.pid oracle_process
, DECODE(lockwait,'','','Y') lockwait
from v$session s
, v$process p
, applsys.fnd_logins n
, applsys.fnd_user u
where s.paddr = p.addr
and n.pid IS NOT NULL
and n.serial# IS NOT NULL
and n.serial# = p.serial#
and n.end_time IS NULL
and s.process = n.spid -- so we don't get hung sessions with old SID and SERIAL
and p.pid = n.pid
and n.user_id = u.user_id

! !! Will only work if AUDIT is set for USER Level !!!
To do this set:
navigator(Sys Admin Resp)
-Profile
-System
-Find System Profile Values (FORM)

-System Profile Values (FORM)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/207/viewspace-1015501/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/207/viewspace-1015501/