2019年8月3日-EBS根据request_id找跟踪文件

如题，借鉴网络提供sql，修正sql如下：

-- 刚写完就被打脸了，user_dump_dest  这个参数在rac环境中一直是PROD01 ，后续继续调整吧

SELECT req.request_id,
       req.oracle_process_id Trace_id,
       req.enable_trace Trace_Flag,
       dest.value || '/' || regexp_substr(dest.value, 'PROD0[1-2]') /* 2019-8-3 升级版，若为rac 则使用正则从文件目录中获取单节点的实例名。若非rac，需修改正则的匹配规则 */
       || '_ora_' || oracle_process_id || '.trc' "Trace_File_Name",
       prog.user_concurrent_program_name "Conc_Prog_Name",
       execname.execution_file_name || execname.subroutine_name "File_Name",
       DECODE(phase_code, 'R', 'Running') || '-' ||
       DECODE(status_code, 'R', 'Normal') "Status",
       ses.sid || ',' || ses.serial# SID_Serial,
       ses.MODULE "SID_Serial_Module"
  FROM fnd_concurrent_requests    req,
       v$session                  ses,
       v$process                  proc,
       v$parameter                dest,
       v$parameter                dbnm,
       fnd_concurrent_programs_vl prog,
       fnd_executables            execname
 WHERE req.request_id = 216517830
   AND req.oracle_process_id = proc.spid(+)
   AND proc.addr = ses.paddr(+)
   AND dest.name = 'user_dump_dest'
   AND dbnm.name = 'db_name'
   AND req.concurrent_program_id = prog.concurrent_program_id
   AND req.program_application_id = prog.application_id
   AND prog.application_id = execname.application_id(+) /*2019-8-3  根据业务提供request id ，内连接导致未找到文件，改为外连接*/
   AND prog.executable_id = execname.executable_id(+);