Oracle EBS併發請求啟用跟蹤

文章版權所有 Jusin Hao（luckyfriends） ，支援原創，轉載請註明

1.1. 併發請求啟用跟蹤

AP_EXPENSE_REPORT_HEADERS_ALL (檢查事件預警)

1369519

請求Output和Log存放的路徑查詢
SELECT T.LOGFILE_NAME

,T.OUTFILE_NAME

FROM FND_CONCURRENT_REQUESTS T

WHERE T.REQUEST_ID = '1369499';

如果要對FORM的操作做TRACE操作，可以使用 幫助->診斷->跟蹤 中啟用跟蹤功能來實現。

但是如果要實現對併發請求的trace，需要在 系統管理員->併發->方案->定義 裡找到對應的併發請求，並勾選”啟用跟蹤”項。然後提交這個併發請求，系統就會生成併發請求的trace檔案。一個複雜的併發請求，會生成一個很大的trace檔案，所以在做完trace後，記得勾除併發請求的“啟用跟蹤”項。

用如下的SQL來實現查詢併發請求的TRACE檔案：

SELECT 'Request id: ' || Request_Id,

'Trace id: ' || Oracle_Process_Id,

'Trace Flag: ' || Req.Enable_Trace,

'Trace Name:' || Dest.Value || '/' || Lower(Dbnm.Value) || '_ora_' || Oracle_Process_Id || '_ANONYMOUS.trc',

'Prog. Name: ' || Prog.User_Concurrent_Program_Name,

'File Name: ' || Execname.Execution_File_Name || Execname.Subroutine_Name,

'Status : ' || Decode(Phase_Code, 'R', 'Running') || '-' ||

Decode(Status_Code, 'R', 'Normal'),

'SID Serial: ' || Ses.Sid || ',' || Ses.Serial#,

'Module : ' || Ses.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 = &REQUEST_ID

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

AND Prog.Executable_Id = Execname.Executable_Id

--- EBS 單例項上所有正在執行的併發請求以及請求目前的狀態

select w.seconds_in_wait "Secondswait",

w.event "waitEvent",

w.p1 || chr(10) || w.p2 || chr(10) || w.p3 "Session Wait",

p.spid || chr(10) || s.process "ServerClient",

s.sid || chr(10) || s.serial# || chr(10) || s.sql_hash_value "SidSerialSQLHash",

u.user_name || chr(10) || PHASE_CODE || ' ' || STATUS_CODE ||

chr(10) || s.status "DBPhaseStatusCODEUser",

Request_id || chr(10) || priority_request_id || chr(10) ||

Parent_request_id "Request_id",

concurrent_program_name,

user_concurrent_program_name,

requested_start_Date || chr(10) ||

round((sysdate - requested_start_date) * 1440, 2) || 'M' "RequestStartDate",

ARGUMENT_TEXT,

CONCURRENT_QUEUE_ID,

QUEUE_DESCRIPTION

FROM FND_CONCURRENT_WORKER_REQUESTS,

fnd_user u,

gv$session s,

gv$process p,

gv$session_wait w

WHERE (Phase_Code = 'R')

and hold_flag != 'Y'

and Requested_Start_Date <= SYSDATE

AND ('' IS NULL OR

('' = 'B' AND PHASE_CODE = 'R' AND STATUS_CODE IN ('I', 'Q')))

and '1' in (0, 1, 4)

and requested_by = u.user_id

and s.paddr = p.addr

and s.sid = w.sid

and oracle_process_id = p.spid

and oracle_session_id = s.audsid

order by requested_start_date;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/14710393/viewspace-1180271/，如需轉載，請註明出處，否則將追究法律責任。