/*
 * Title  : sho_ses_trc.sql
 * Author : EXADIME LLC
 * Created: 29-Jul-2017
 * Updated: 29-Jul-2017
 * Release: 1.0.0
 * Purpose: Shows session trace files.
 * History: N/A
 */

start oracle.ini

set echo off
set feedback off
set serverout on

set linesize 120
set pagesize 24

set heading on
set headsep on
set underline on
set colsep " "

set verify off
set escape off
set embedded on

col "Trace File"	format a60

accept p_sid prompt 'Enter SID '

spool &&rpt_dir/sho_ses_trc.rpt

select 
	a.value || '/' || b.value || '_ora_' || p.spid || nvl2(p.traceid, '_' || p.traceid, null) || '.trc' "Trace File"
from 
	V$PARAMETER a 
	cross join V$PARAMETER b 
	cross join V$PROCESS p
	join V$SESSION s on p.addr = s.paddr 
where 
	a.name = 'user_dump_dest' 
and	b.name = 'instance_name' 
and	s.sid=&&p_sid;

spool off;

clear columns

set embedded off
set escape off
set verify on

set colsep " "
set underline on
set headsep on
set heading on

set pagesize 24
set linesize 80

set serverout off
set feedback on
set echo off
