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

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

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

C.VALUE||'\ORA'||TO_CHAR(A.SPID,'FM00000')||'.TRC'
-----------------------------------------------------------------------------------------------------------------------------------
C:\ORACLEXE\APP\ORACLE\ADMIN\XE\UDUMP\ORA01476.trc


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

SQL>
SQL>