EBS(ERP)根據請求號(request ID)查詢正在執行的sql

kisslfcr發表於2015-07-31
SQL
 ORACLE EBS的Request和後臺資料庫Session透過apps.fnd_concurrent_requests.oracle_process_id欄位和v$process.spid欄位關聯。[@more@]

下面的SQL可以根據Request ID找到對應的Session資訊:-
select *
from v$session
where paddr in
(select addr
from v$process
where spid =
(select b.oracle_process_id
from apps.fnd_concurrent_requests b
where b.request_id = &req_number))

或者進一步找到正在執行的SQL:-
select sql_text
from v$sqltext_with_newlines
where hash_value in
(select SQL_HASH_VALUE
from v$session
where paddr in
(select addr
from v$process
where spid =
(select b.oracle_process_id
from apps.fnd_concurrent_requests b
where b.request_id = &req_number)))
order by piece;

朋友提供，較為準確：
SELECT ses.sid, 
        ses.serial# , 
        Ses.sql_id 
     FROM v$session ses, 
        v$process pro 
        WHERE ses.paddr = pro.addr 
             AND pro.spid IN (SELECT oracle_process_id 
                  FROM fnd_concurrent_requests 
                  WHERE request_id = <request_id>);
select sql_text from v$sqltext where sql_id='0kkhhb2w93cx0' order by piece

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

相關文章