概述

在我们日常工作中，我们经常根据SPID查询SQL文本、sid查询SQL文本等,本文分享一下使用的方法。

解决方案

根据 (SPID)查询执行的SQL

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S
WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND     s.sid in (select s.sid from v$session s , v$process p
where   p.addr =s.paddr
and     p.spid in (&ProcessID)) ORDER BY S.SID, T.PIECE;

根据 SID查询执行的SQL

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT  T.SQL_TEXT FROM v$SQLTEXT T, v$SESSION S
WHERE   S.SQL_ADDRESS=T.ADDRESS and s.status ='ACTIVE'
AND     s.sid in (&sid) ORDER BY S.SID, T.PIECE;

查询长事务运行的SQL

set pages 1000
set lines 120
col sid for 99999
col username for a20
col sql_text for a80

SELECT      s.sid, s.username, T.SQL_TEXT
FROM v$SQLTEXT T, v$SESSION S
WHERE       S.SQL_ADDRESS=T.ADDRESS
and   s.status ='ACTIVE'
AND   s.sid in (select sid  from v$session_longops where time_remaining>0)
ORDER BY S.SID, T.PIECE;

Tags：oracle查询库