Oracle PL/SQL Tutorial/System Tables Data Dictionary/process

Join v$process, v$session and v$parameter

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>