[20171102]视图v$session中process字段含义.txt
--//被别人问及这个问题,自己也简单测试看看.我记忆里看warehouse的教学视频,好像提到的client端的进程号.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.使用本地登录看看.
sqlplus scott/book
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SPID PID P_SERIAL# C50
---------- ---------- ------------------------ ------ ------- ---------- --------------------------------------------------
54 611 12507 12508 28 222 alter system kill session '54,611' immediate;
--//可以发现进程号是12508,PROCESS=12507.
$ cat spid.sql
SELECT s.sid, s.serial#, s.process, p.spid, p.pid, p.serial# p_serial#,'alter system kill session '''||s.sid||','||s.serial#||''''||' immediate;' c50
FROM v$session s, v$process p
WHERE s.sid in (
SELECT sid
FROM v$mystat
WHERE rownum = 1)
AND s.paddr = p.addr;
$ ps -ef | grep 1250[78]
oracle 12507 12503 0 09:20 pts/7 00:00:00 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
oracle 12508 12507 0 09:20 ? 00:00:00 oraclebook (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
--//可以发现使用本地连接通过beq协议连接,sqlplus的进程号12507,再通过这个进程连接数据库,对应进程号是12508.(注意其父进程是12507)
3.测试使用网络连接(注在本机):
sqlplus scott/book@book
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SPID PID P_SERIAL# C50
---------- ---------- ------------------------ ------ ------- ---------- --------------------------------------------------
54 613 12601 12603 28 223 alter system kill session '54,613' immediate;
--//可以发现进程号是12601,PROCESS=12601.
$ ps -ef | grep 1260[13]
oracle 12601 12597 0 09:34 pts/7 00:00:00 /u01/app/oracle/product/11.2.0.4/dbhome_1/bin/sqlplus
oracle 12603 1 0 09:34 ? 00:00:00 oraclebook (LOCAL=NO)
--//可以发现使用TCP连接,sqlplus的进程号12601,通过监听连接数据库,连接数据库的进程号是12508.(注意其父进程是1).
4.测试从client windows连接数据库.
sqlplus scott/book@192.168.100.78:1521/book:DEDICATED
SCOTT@192.168.100.78:1521/book:DEDICATED> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
54 629 7132:3244 DEDICATED 12810 28 230 alter system kill session '54,629' immediate;
D:\tools\sysinternals>tasklist /FI "PID eq 7132"
映像名称 PID 会话名 会话# 内存使用
========================= ======== ================ =========== ============
sqlplus.exe 7132 Console 1 17,952 K
--//3244来自那里呢?实际上tid,我对windows不是非常熟悉.
--//windows的工具包sysinternals中procexp可以找到对应tid.具体细节略.
$ ps -ef | grep 1281[0]
oracle 12810 1 0 10:03 ? 00:00:00 oraclebook (LOCAL=NO)
5.测试从client windows连接使用共享模式.
sqlplus scott/book@192.168.100.78:1521/book:shared
SCOTT@192.168.100.78:1521/book:shared> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
261 3 3140:4464 SHARED 5752 20 1 alter system kill session '261,3' immediate;
D:\tools\sysinternals>tasklist /FI "PID eq 3140"
映像名称 PID 会话名 会话# 内存使用
========================= ======== ================ =========== ============
sqlplus.exe 3140 Console 1 17,988 K
$ ps -ef | grep 575[2]
oracle 5752 1 0 Nov01 ? 00:00:00 ora_s000_book
--//进程号5752对应是ora_s000_book.