33 lines
888 B
SQL
33 lines
888 B
SQL
/*
|
|
This script is to list the trace file name for every process. It takes the SPID and displays the complete trace file name, if one would exists.
|
|
*/
|
|
|
|
SET echo OFF lines 150
|
|
COLUMN username FORMAT a10
|
|
COLUMN trace_file FORMAT a70
|
|
|
|
SELECT s.username
|
|
, s.sid
|
|
, s.serial#
|
|
, p.spid
|
|
, udump.VALUE
|
|
|| DECODE ( SIGN ( INSTR ( udump.VALUE, '\' ) - 0 )
|
|
-- If `\` exists in path then use '\' as directory separator
|
|
,1, '\'
|
|
, '/'
|
|
)
|
|
|| LOWER ( db.VALUE )
|
|
|| '_ora_'
|
|
|| TO_CHAR ( p.spid, 'fm00000' )
|
|
|| '.trc' "TRACE_FILE"
|
|
FROM v$process p
|
|
, v$session s
|
|
, v$parameter udump
|
|
, v$parameter db
|
|
WHERE p.addr = s.paddr
|
|
AND udump.NAME = 'user_dump_dest'
|
|
AND db.NAME = 'db_name'
|
|
AND s.username IS NOT NULL;
|
|
|
|
SET echo ON
|