Join v$process, v$session, v$parameter and v$instance : parameter « System Tables Data Dictionary « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Tables Data Dictionary
3. parameter

SQL>
SQL> select rtrim(c.value,'/') || '/' || d.instance_name ||
  2                      '_ora_' || ltrim(to_char(a.spid)) || '.trc'
  3    from v$process a, v$session b, v$parameter c, v$instance d
  4   where a.addr = b.paddr
  5     and b.audsid = sys_context( 'userenv', 'sessionid')
  6     and c.name = 'user_dump_dest'
  7     and rownum < 50
  8  /

RTRIM(C.VALUE,'/')||'/'||D.INSTANCE_NAME||'_ORA_'||LTRIM(TO_CHAR(A.SPID))||'.TRC'
-----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP/xe_ora_1476.trc


Statistics
----------------------------------------------------------
        913  recursive calls
          0  db block gets
        369  consistent gets
          0  physical reads
          0  redo size
        535  bytes sent via SQL*Net to client
        380  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         22  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>