[20171102]檢視v$session中process欄位含義

[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.