v$session process column’s format xxxx:yyyy

今天在使用PLSQL developer的时候发现一个有趣的现象,在使用PLSQL初次登录数据库后,在v$session中的action字段会记录为Primary Session,当初始化SQL Window和Command Window的时候并执行语句之后,在v$session中的action字段会记录对应操作类型的数据:SQL Window – New和Command Window – New。
测试环境为Oracle 11.2.0.4 + Windows7 + PLSQL DEVELOPER9

SQL> col process for a10
SQL> col spid for a10
SQL> col module for a20
SQL> col action for a20
SQL>
SQL> select s.saddr,
  2         s.sid,
  3         s.serial#,
  4         s.PADDR,
  5         s.process,
  6         s.PORT,
  7         s.module,
  8         s.action,
  9         p.PID,
 10         p.SPID
 11    From v$session s, v$process p
 12   where s.PADDR = p.ADDR
 13     and s.username = 'OE';

SADDR                   SID    SERIAL# PADDR            PROCESS          PORT MODULE               ACTION                      PID SPID
---------------- ---------- ---------- ---------------- ---------- ---------- -------------------- -------------------- ---------- ----------
000000012679D3C0       2377      48789 00000001270EF7D8 4928:3556       57720 PL/SQL Developer     SQL Window - New            524 6495
00000001289CB350         31      52907 00000001271CAEC8 4928:3556       57435 PL/SQL Developer     Primary Session             944 6085
0000000126165BE0       1155      33125 00000001271D0260 4928:3556       57451 PL/SQL Developer     SQL Window - New            954 7765
00000001242C1FA0       4041      59393 000000012826CE30 4928:3556       57735 PL/SQL Developer     SQL Window - New           1015 11196
0000000125E4BDC0       3428      60151 00000001271F3AD0 4928:3556       57765 PL/SQL Developer     Command Window - New       1022 13257

SQL>

查看进程4928在windows上的端口信息

C:\>netstat -ano | findstr 4928
  TCP    192.168.2.59:57435     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57451     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57720     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57735     192.168.1.10:1521      ESTABLISHED     4928
  TCP    192.168.2.59:57765     192.168.1.10:1521      ESTABLISHED     4928
  UDP    127.0.0.1:53149        *:*                                  4928

查询进程4928在windows的线程信息。

C:\PSTools>pslist -dmx 4928

PsList v1.4 - Process information lister
Copyright (C) 2000-2016 Mark Russinovich
Sysinternals - www.sysinternals.com

Process and thread information for PC-20161024TGGX:

Name                Pid      VM      WS    Priv Priv Pk   Faults   NonP Page
plsqldev           4928  232312   81728   71140   71524    36987     57  394
 Tid Pri    Cswtch            State     User Time   Kernel Time   Elapsed Time
3556  10  28148940     Wait:UserReq  0:00:07.597   0:00:13.899    6:02:12.939
1368   7         1   Wait:Suspended  0:00:00.000   0:00:00.000    6:02:12.775
6516  12      1180     Wait:UserReq  0:00:00.000   0:00:00.015    6:02:12.705
 828   8         2     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:12.598
1284   9       288   Wait:Suspended  0:00:00.015   0:00:00.000    6:02:04.045
5608  11        33     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:04.020
6400   9      9356     Wait:UserReq  0:00:00.000   0:00:00.000    6:02:04.014
3432   8       534       Wait:Queue  0:00:00.046   0:00:00.000    6:02:03.975
5448   9        30       Wait:Queue  0:00:00.000   0:00:00.000    6:02:03.361
4588   9        87   Wait:Suspended  0:00:00.000   0:00:00.000    6:01:47.594
6328  10         5   Wait:Suspended  0:00:00.000   0:00:00.000    3:37:26.221
7896  10       124   Wait:Suspended  0:00:00.000   0:00:00.000    3:16:04.232
7864  10         5   Wait:Suspended  0:00:00.000   0:00:00.000    3:12:55.948
4944  10        27   Wait:Suspended  0:00:00.000   0:00:00.000    3:11:20.407
8508   8        13       Wait:Queue  0:00:00.000   0:00:00.000    0:02:39.330
5424   8        26       Wait:Queue  0:00:00.000   0:00:00.000    0:02:39.010
5700   8         8       Wait:Queue  0:00:00.000   0:00:00.000    0:00:55.701

在PL/SQL DEVELOPER登录到数据库后,Process的内容是固定的,格式为XXXX:YYYY,都是windows下PL/SQL DEVELOPER所在的进程号+主线程号。
当通过PLSQL DEVELOPER通过LOG OFF和LOG ON登录新的用户之后,原有的PROCESS保持不变,v$session中的其他信息改变。
关于windows的进程和线程介绍请查看微软文档关于Processes and Threads的说明。

Footnote

v$session视图对应x$ksuse固态表,process字段对应其ksusepid列。

此条目发表在Infrastructure, PLSQL分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s