和某個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)