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

测试环境

主机: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]

客户端发起