获取同一个transaction中的所有SQL

Question

session1的transaction正在执行，尚未commit/rollback。如何能在另一个session中获取session1的当前transaction的所有SQL语句？例如：新建立一个session，
UPDATE T1 SET C1 = 2 WHERE C1 = 1;
-- T2有很多数据，C2列上没有index，所以会走full table scan，DELETE花费很长时间。
DELETE FROM T2 WHERE C2 <> -2;
这是，不commit/rollback。另外建立一个session，
通过v$session能查询到上面session的信息，
v$session.SQL_ADDRESS和SQL_HASH_VALUE关联v$SQL的ADDRESS和HASH_VALUE能获得当前正在执行的SQL语句"DELETE FROM T2...."；
但是通过v$session.prev_SQL_ADDRESS和Prev_SQL_HASH_VALUE得到的不是我想要的"UPDATE T1..."，而是oracle内部对系统表操作的语句。如何能获取"UPDATE T1 SET C1 = 2 WHERE C1 = 1"这句SQL语句？

Answer 1

通过Prev_SQL_HASH_VALUE可以得到的，我这里用了很长时间都没有问题啊，你把语句贴出来看看

Answer 2

SELECT
TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss') select_date,
SES.SID,
SES.SERIAL#,
SES.MACHINE,
SES.USERNAME,
SES.PROGRAM,
SES.STATUS,
SC.SQL_TEXT current_SQL,
SP.SQL_TEXT prev_SQL
FROM
V$SQL SC,
V$SQL SP,
V$SESSION SES
where
SC.ADDRESS = SES.SQL_ADDRESS
AND SC.HASH_VALUE = SES.SQL_HASH_VALUE
AND SP.ADDRESS = SES.PREV_SQL_ADDR
AND SP.HASH_VALUE = SES.PREV_HASH_VALUE
AND SES.STATUS IN ('ACTIVE')
AND NOT EXISTS (
SELECT
1
FROM
V$SQLTEXT ST
WHERE
SC.ADDRESS = ST.ADDRESS
AND SC.HASH_VALUE = ST.HASH_VALUE
AND (ST.SQL_TEXT LIKE '%:%' OR UPPER(ST.SQL_TEXT) LIKE '%WHERE%')
)ORDER BY
SES.PROGRAM,
SES.SID,
SES.SERIAL#

Answer 3

你的语句太复杂了，完全可以很简单啊你指出一个问题，在运行完delete之后，你的session的状态就不会是 ACTIVE了：
AND SES.STATUS IN ('ACTIVE')

Answer 4

select (select bb.SQL_TEXT
          from v$sql bb
         where bb.IS_OBSOLETE = 'N'
           and bb.CHILD_NUMBER = 0
           and bb.HASH_VALUE =
               (select aa.PREV_HASH_VALUE from v$session aa where aa.SID = 11)) pre_sql,
       (select bb.SQL_TEXT
          from v$sql bb
         where bb.IS_OBSOLETE = 'N'
           and bb.CHILD_NUMBER = 0
           and bb.HASH_VALUE =
               (select aa.SQL_HASH_VALUE from v$session aa where aa.SID = 11)) pre_sql
  from dual tt
这样就可以了，语句这么复杂，我看不明白

Answer 5

多谢啦！我的举例中，在查询session时，delete需要运行一两个小时，尚未结束。
所以我在贴SQL时，把'INACTIVE'删去了。

Answer 6

在真实环境下，查询session时是不知道SID的。
我做个试验试一试你的SQL。

Answer 7

我用的是Oracle 8.0.5.0.0，非常古老的版本。v$sql没有IS_OBSOLETE列。
为什么要加 v$sql.CHILD_NUMBER = 0这个限制？

Answer 8

我将4楼的SQL中对IS_OBSOLETE列和CHILD_NUMBER列的限制去掉。
运行后，得到的一句SQL是正在DELETE的SQL，
另外一句如下：
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),groups=decode(:14, 65535, NULL, :14), cachehint=:15 where ts#=:1 and file#=:2 and block#=:3
我用我的查询SQL得到的也是类似的对系统表进行操作的语句。

Answer 9

直接数据字典表中恐怕是找不到那么多信息了，你用logmine对redo log进行mine吧，这里详细的记录了一个事务做的变更，不过，这里恐怕也只能找到变更信息，query之类的语句恐怕是找不到的

Answer 10

我目前就是用logMiner archive redo log file在抓SQL语句。
得到的WHERE子句并不是实际的SQL，只能保证与实际SQL等效。
现在就是想抓住实际SQL，能帮助我们找出程序的bug。
（我没有源代码。有源代码的傻人说没有实际的SQL就找不出bug。kao!）
（最近，系统会发生DELETE FROM T1这种疯狂的动作。我利用logMiner发现在同一个transaction中，DELETE之前会有一、二句UPDATE T2。用Java写了个小程序，每5分钟检查v$session表，如果有“DELETE FROM T1”这句话，就kill session。T1中数据挺多的，DELETE会超过1、2个小时，所以这个工具还是挺管用的。现在想进一步得到实际的UPDATE语句，来帮助debug）

Answer 11

狂晕，这个写代码的人够傻的，这不是本末倒置了吗？你可以找到delete请求是从哪台机器发出来的啊，直接找到那个家伙

Answer 12

整个系统是三层的，前端用pb，中间层是SUN FORTE(用FORTE TOOL写的，我以前从来没有听说过这种编程语言，估计中国会用这种语言的人不超过个位数吧)，数据库是oracle(版本是8.0.5，够古老吧)。利用Toad抓到"DELETE FROM"是从forte的ftexec.exe这个进程发出来的。目前查不出end user。再说，end user不会是故意要DELETE，又不是黑客。
我目前所能做的只能是找出实际的SQL。
哪位能提个思路。谢谢！

Answer 13

通过v$session表查询该session正在执行的语句不行吗？