查找客户端未提交的事务语句

测试环境 主机:cent os 4.3 (192.168.1.33) 数据库版本:Version 10.2.0.1.0 windows客户端(192.168.1.11) 做这个测试之前，先熟悉以下的动态性能表(可参考10gR2文档) v$session (lists session information for each current session) v$transaction (lists the active transactions in the system) v$process (contains information about the currently active processes) v$sql (lists statistics on shared SQL area without the GROUP BY clause and contains one row for each child of the original SQL text entered) 查看当前连接的会话状态 SQL> select sid,username,status from v$session where username is not null;        SID USERNAME                       STATUS ---------- ------------------------------ --------        159 SYS                            ACTIVE 启动监听，让windows客户端能连接上来 SQL> !lsnrctl start 然后在windows上用scott连接上来 在192.168.1.33上用netstat查看当前进程状态(netstat -anp) [oracle@centos ~]$ netstat -anp (Not all processes could be identified, non-owned process info  will not be shown, you would have to be root to see it all.) Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   tcp        0      0 0.0.0.0:32768               0.0.0.0:*                   LISTEN      -                   tcp        0      0 0.0.0.0:32769               0.0.0.0:*                   LISTEN      2770/ora_d000_lihui tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN      -                   tcp        0      0 0.0.0.0:1521                0.0.0.0:*                   LISTEN      2981/tnslsnr        tcp        0      0 127.0.0.1:631               0.0.0.0:*                   LISTEN      -                   tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      -                   tcp        0      0 127.0.0.1:32795             127.0.0.1:1521              ESTABLISHED 2748/ora_pmon_lihui tcp        0      0 192.168.1.33:1521           192.168.1.11:1042           ESTABLISHED 3155/oraclelihui    tcp        0      0 127.0.0.1:1521              127.0.0.1:32795             ESTABLISHED 2981/tnslsnr        tcp        0      0 :::22                       :::*                        LISTEN      -                   tcp        0      0 ::ffff:192.168.1.33:22      ::ffff:192.168.1.11:1041    ESTABLISHED - ............................... ...............以下省略 查看具体连接到oracle的哪个进程 [oracle@centos ~]$ ps -ef |grep 3155 oracle    3155     1  0 05:39 ?        00:00:00 oraclelihui (LOCAL=NO) oracle    3696  2682  0 05:55 pts/0    00:00:00 grep 3155 在windows上也可以查看连接到192.168.1.33上的进程: C:/>netstat -b   TCP    china:1042             192.168.1.33:1521      ESTABLISHED     2148 [sqlplus.exe] 客户端发起连接(192.168.1.11)(但不要commit，保持事务) SQL> conn scott/tiger@lihui 已连接。 SQL> create table m (id number(5),name char(10)); 表已创建。 SQL> insert into m values (0,'test'); 已创建 1 行。 此时不要commit提交。 服务端查询(192.168.1.33) SQL> select addr,ses_addr from v$transaction; ADDR     SES_ADDR -------- -------- 28967184 29F056AC SQL> select saddr,sid,paddr,username,status from v$session where username is not null; SADDR           SID PADDR    USERNAME                       STATUS -------- ---------- -------- ------------------------------ -------- 29F056AC        154 29E1DA40 SCOTT                          INACTIVE 29F0B430        159 29E1C370 SYS                            ACTIVE SQL> select sid,prev_sql_addr,username,status from v$session   2  where username is not null;        SID PREV_SQL USERNAME                       STATUS ---------- -------- ------------------------------ --------        154 26A365C8 SCOTT                          INACTIVE        159 26A35DD0 SYS                            ACTIVE SQL> select sql_text,address from v$sql   2  where address='26A365C8'; SQL_TEXT -------------------------------------------------------------------------------- ADDRESS -------- insert into m values (0,'test') 26A365C8 到此查到了正在进行transaction的sql语句。 附： select addr,ses_addr from v$transaction; select saddr,sid,serial#,username,status from v$session where username is not null; select saddr,sid,serial#,username,status,prev_sql_addr,prev_hash_value from v$session where username is not null; select addr,sid,username,s.status,process,program from v$transaction t,v$session s where t.ses_addr=s.saddr; select addr,pid,spid,program from v$process; select saddr,sid,paddr,username,status from v$session where username is not null; select sql_text,address,hash_value from v$sql q,v$session s where s.sid=154 and s.prev_sql_addr=q.address;