EBS(ERP)根据请求号(request ID)查找正在运行的sql

75 阅读 0 评论 50 点赞

我是靠谱客的博主细心自行车，最近开发中收集的这篇文章主要介绍EBS(ERP)根据请求号(request ID)查找正在运行的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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29135257/viewspace-1757347/