【Oracle】跟踪文件目录(User_Dump_Dest)生成脚本（11g/9i).txt

oracle 11g 生成跟踪目录(User_Dump_Dest)脚本：
–设置合适显示长度：
column trace new_val TRACE format a100
–脚本：

select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

    from v$process a, v$session b, v$parameter c, v$instance d

   where a.addr = b.paddr

     and b.audsid = userenv('sessionid')

     and c.name = 'user_dump_dest'

/

–开启跟踪
exec dbms_monitor.session_trace_enable
–查看生成的跟踪文件目录
!ls &TRACE

例如：

SYS@ORA11GR2> column trace new_val TRACE format a100

SYS@ORA11GR2>

SYS@ORA11GR2> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace

  2      from v$process a, v$session b, v$parameter c, v$instance d

  3     where a.addr = b.paddr

  4       and b.audsid = userenv('sessionid')

  5       and c.name = 'user_dump_dest'

  6  /

TRACE

----------------------------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_4146.trc

SYS@ORA11GR2> exec dbms_monitor.session_trace_enable

PL/SQL procedure successfully completed.

SYS@ORA11GR2> !ls &TRACE

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_4146.trc

oracle 9i 生成跟踪目录(User_Dump_Dest)脚本：
–设置合适显示长度：
column trace new_val TRACE format a100
–脚本：

select c.value || 'ora_' || a.spid || '.trc' trace

from v$process a, v$session b, v$parameter c

   where a.addr = b.paddr

     and b.audsid = userenv('sessionid')

     and c.name = 'user_dump_dest'

/

–开启跟踪
exec dbms_monitor.session_trace_enable
–查看生成的跟踪文件目录
!ls &TRACE