根据操作系统SPID查询对应的SQL语句

依据操作系统SPID查询正在运行的SQL语句

---根据spid查询对应的SQL语句
SELECT P.pid pid,
       S.sid sid,
       P.spid spid,
       S.username username,
       S.osuser osname,
       P.serial# S_#,
       P.terminal,
       P.program program,
       P.background,
       S.status,
       RTRIM(SUBSTR(a.sql_text, 1, 80)) SQL
  FROM v$process P, v$session S, v$sqlarea A
 WHERE P.addr = s.paddr
   AND S.sql_address = a.address(+)
   AND P.spid=1268;

select /*+ ORDERED */
 sql_text, sql_id
  from v$sqltext a
 where (a.hash_value, a.ADDRESS) in
       (select decode(sql_hash_value, 0, PREV_HASH_VALUE, sql_hash_value),
               decode(sql_hash_value, 0, PREV_SQL_ADDR, SQL_ADDRESS)
          from v$session b
         where b.paddr =
               (select addr from v$process c where c.spid = '&spid'))
 order by piece asc;
  
---根据会话sid查询对应的SQL语句
select /*+ ORDERED */
 sql_text, sql_id
  from v$sqltext a
 where (a.hash_value, a.ADDRESS) in
       (select decode(sql_hash_value, 0, PREV_HASH_VALUE, sql_hash_value),
               decode(sql_hash_value, 0, PREV_SQL_ADDR, SQL_ADDRESS)
          from v$session b
         where b.sid = &SID)
 order by piece asc;

 
 具体步骤:
(1) 根据操作系统进程ID查询进程地址
   SELECT addr FROM v$process c WHERE c.spid = 82190 ;

(2) 根据进程地址查询sql_hash_value
SELECT DECODE(sql_hash_value, 0, prev_hash_value, sql_hash_value),
       DECODE(sql_hash_value, 0, prev_sql_addr, sql_address)
  FROM v$session b
 WHERE b.paddr = '00000009C913BD10';
 
(3) 根据sql_hash_value查询对应的SQL语句
select a.sql_text,a.sql_id From v$sqltext a where a.hash_value='3872658090 ' and a.address='00000008CF0871E0' order by piece  ;