6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

Oracle のセッション ID と OSPID を紐づける方法

Last updated at Posted at 2015-09-20

Oracle のセッション ID と OSPID を紐づける方法

Oracle の v$session で出力される情報は SID(セッション ID)をベースとしてます。ただし、各プロセスの CPU 使用率を追う場合には、SID と OSPID を紐づける必要があります。

紐づけたい場合は以下の SQL で可能です。SPID 列が OSPID に相当します。

select sid, pid, spid, s.username, osuser, s.program 
  from v$process p, v$session s 
 where p.addr=s.paddr;
output
 SID PID SPID  USERNAME OSUSER   PROGRAM
---- --- ----- -------- -------- -------------------------------
   2   2 5537           ora11204 oracle@ol66.local (PMON)
   3   3 5539           ora11204 oracle@ol66.local (PSP0)
   4   4 5541           ora11204 oracle@ol66.local (VKTM)
   5   5 5545           ora11204 oracle@ol66.local (GEN0)
   6   6 5547           ora11204 oracle@ol66.local (DIAG)
   7   7 5549           ora11204 oracle@ol66.local (DBRM)
   8   8 5551           ora11204 oracle@ol66.local (DIA0)
   9   9 5553           ora11204 oracle@ol66.local (MMAN)
  10  10 5555           ora11204 oracle@ol66.local (DBW0)
  11  11 5557           ora11204 oracle@ol66.local (LGWR)
  12  12 5559           ora11204 oracle@ol66.local (CKPT)
  13  13 5561           ora11204 oracle@ol66.local (SMON)
  14  14 5563           ora11204 oracle@ol66.local (RECO)
  15  15 5565           ora11204 oracle@ol66.local (MMON)
  16  16 5567           ora11204 oracle@ol66.local (MMNL)
  29  19 13195          ora11204 oracle@ol66.local (Q002)
  18  20 5579           ora11204 oracle@ol66.local (ARC0)
  20  21 5581           ora11204 oracle@ol66.local (ARC1)
  21  22 5583           ora11204 oracle@ol66.local (ARC2)
  23  23 5585           ora11204 oracle@ol66.local (ARC3)
  28  24 5587           ora11204 oracle@ol66.local (QMNC)
  38  25 5648           ora11204 oracle@ol66.local (SMCO)
  50  26 13986 SYS      ora11204 sqlplus@ol66.local (TNS V1-V3)
  45  28 13715          ora11204 oracle@ol66.local (W001)
  37  30 5613           ora11204 oracle@ol66.local (Q001)
  31  31 6002           ora11204 oracle@ol66.local (CJQ0)

念のため正しいことを確認。

$ ps -ef | grep 5537 | grep -v grep
ora11204  5537     1  0 00:01 ?        00:00:05 ora_pmon_orcl

セッション ID の確認

また、接続しているセッションの SID を確認した場合は以下で可能です。

select distinct sid from v$mystat;
output
       SID
----------
        50

深堀

取得する機会は少ないかもしれませんが、system state を取得することでも SID と OSPID を紐づけることが可能です。

systemstate
SQL> alter session set events 'immediate trace name systemstate level 266';
orcl_ora_14000.trc
  :
PROCESS 2: PMON
  ----------------------------------------
  SO: 0x784a6868, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x784a6868, name=process, file=ksu.h LINE:12721, pg=0
  (process) Oracle pid:2, ser:1, calls cur/top: 0x780c5818/0x780c5818
            flags : (0xe) SYSTEM
            flags2: (0x0),  flags3: (0x10)
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 0 0 281
              last post received-location: kmmcts.h LINE:2898 ID:kmmreg()
              last process to post me: 0x784b84a0 53 2
              last post sent: 0 0 26
              last post sent-location: ksa2.h LINE:285 ID:ksasnd
              last process posted by me: 0x784a7920 1 6
    (latch info) wait_event=0 bits=0x0
    Process Group: DEFAULT, pseudo proc: 0x7854deb0
    O/S info: user: ora11204, term: UNKNOWN, ospid: 5537 <--★ OSPID=5537
    OSD pid info: Unix process pid: 5537, image: oracle@ol66.local (PMON)
  :
    SO: 0x78080600, type: 4, owner: 0x784a6868, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x784a6868, name=session, file=ksu.h LINE:12729, pg=0
    (session) sid: 2 ser: 1 trans: (nil), creator: 0x784a6868 <--★ SID=2
              flags: (0x51) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
              flags2: (0x409) -/-/INC
              DID: 0002-, short-term DID:
              txn branch: (nil)
              edition#: 0              oct: 0, prv: 0, sql: (nil), psql: (nil), user: 0/SYS
    ksuxds FALSE at location: 0
    service name: SYS$BACKGROUND
  :
6
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?