Query – User sessions

select distinct
s.inst_id,
s.status,
s.username,
s.sid,
s.serial#,
s.schemaname,
s.osuser,
f.user_name,
p.spid,
f.request_id,
s.process,
s.module,
s.action,
s.machine,
s.program,
u.value || ‘/’ || lower(n.value) || ‘ora‘ || p.spid || ‘.trc’ Trace_File,
t.sql_text
from gv$session s,
gv$process p,
gv$sql t,
(select u.user_name, r.* from apps.fnd_concurrent_requests r, apps.fnd_user u where r.requested_by = u.user_id and phase_code = ‘R’) f,
(select inst_id, value from gv$parameter where name = ‘user_dump_dest’) u,
(select inst_id, value from gv$parameter where name = ‘instance_name’) n
where s.paddr = p.addr
and s.inst_id = p.inst_id
and s.sql_address = t.address(+)
and s.inst_id = t.inst_id(+)
and p.spid = f.oracle_process_id(+)
and s.inst_id = u.inst_id
and s.inst_id = n.inst_id;