How to find Trace File associated with every session the database ?

Following query will list the USER session and trace file Associated with each background session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A40 WRAP HEADING 'SCHEMANAME TYPE|PROGRAM'
COLUMN NAME FORMAT A30 WRAP HEADING 'USERNAME|OSUSER'
COLUMN MACHINE FORMAT A20 WRAP HEADING 'MACHINE|TERMINAL'
COLUMN INSTANCE FORMAT A15 WRAP HEADING 'INST_ID|INST_NAME'
COLUMN SID FORMAT A10 WRAP HEADING 'SID|SERIAL#'
COLUMN PID FORMAT A10 WRAP HEADING 'PID|SPID'
COLUMN LOGON_TIME FORMAT A20 WRAP HEADING 'LOGON_TIME|STATUS'
COLUMN TRACEFILE FORMAT A109 WRAP

select
rpad(rtrim(to_char(vses.SID)),10,' ') || rpad(rtrim(to_char(vses.SERIAL#)),10,' ') as SID,
rpad(rtrim(to_char(vproc.PID)),10,' ') || rpad(rtrim(to_char(vproc.SPID)),10,' ') as PID,
rpad(rtrim(vses.USERNAME),30,' ') || rpad(rtrim(vses.OSUSER),30,' ') as NAME,
rpad(trim(vses.SCHEMANAME),25,' ') || ' ' || rpad(rtrim(vses.TYPE),14,' ') || rpad(rtrim(vses.PROGRAM),40,' ') as PROGRAM,
rpad(rtrim(vses.MACHINE),20,' ') || rtrim(vses.TERMINAL) as MACHINE,
rpad(rtrim(TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS')),20,' ') || rpad(rtrim(vses.STATUS),10,' ') as LOGON_TIME,
rpad(rtrim(to_char(vses.INST_ID)),15,' ') || rpad(rtrim(vinst.INSTANCE_NAME),15,' ') as INSTANCE,
vproc.TRACEFILE,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM
from gv$session vses, gv$process vproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background is null -- Uncomment this to get User Process only.
-- and vproc.background = 1 -- Uncomment this to get Background Process only.
order by vses.type,vses.status,vses.inst_id,vses.USERNAME
/

clear columns

Following query will list the BACKGROUND session and trace file Associated with each user session.

set pages 10000
set lines 180
clear columns

COLUMN PROGRAM FORMAT A25 WRAP
COLUMN OSUSER FORMAT A20 WRAP
COLUMN TERMINAL FORMAT A20 WRAP
COLUMN MACHINE FORMAT A20 WRAP
COLUMN DESCRIPTION FORMAT A40 WRAP
COLUMN TRACEID FORMAT A8 WRAP
COLUMN TRACEFILE FORMAT A62 WRAP
COLUMN PID FORMAT 999999 WRAP
COLUMN SPID FORMAT A8 WRAP
COLUMN LOGON_TIME FORMAT A20 WRAP
COLUMN INST_ID FORMAT 999999
COLUMN INSTANCE_NAME FORMAT A10 HEADING INST_NAME

select
vbgproc.NAME,
vses.SID,
vses.SERIAL#,
vses.PROGRAM,
vproc.PID,
vproc.SPID,
vses.OSUSER,
vses.MACHINE,
vses.TERMINAL,
TO_CHAR(vses.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
vses.INST_ID,
vbgproc.DESCRIPTION,
vproc.PGA_USED_MEM,
vproc.PGA_FREEABLE_MEM,
vproc.PGA_MAX_MEM,
vproc.TRACEID,
vproc.TRACEFILE,
vinst.INSTANCE_NAME
from gv$session vses, gv$process vproc, gv$bgprocess vbgproc, gv$instance vinst
where vses.PADDR=vproc.ADDR
and vses.PADDR=vbgproc.PADDR
and vses.inst_id = vproc.inst_id
and vses.inst_id = vbgproc.inst_id
and vses.inst_id = vinst.instance_number
and vproc.background = 1
order by vses.inst_id,vbgproc.name
/

clear columns