|
测试环境 主机: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; |