Oracle でセッションIDからOSのプロセスIDを調べるSQLをメモ。
set pagesize 1000 set linesize 200 col spid for a4 col sid for 9999 col serial# for 9999 col username for a10 col program for a30 col machine for a30 col osuser for a10 select proc.spid, ses.sid, ses.serial#, ses.username, ses.program, ses.machine, ses.osuser from v$session ses, v$process proc where ses.paddr = proc.addr;
- 実行結果
SPID SID SERIAL# USERNAME PROGRAM MACHINE OSUSER ---- ----- ------- ---------- ------------------------------ ------------------------------ ---------- 769 225 1 oracle@solaris101 (PMON) solaris101 oracle 771 224 1 oracle@solaris101 (PSP0) solaris101 oracle 773 223 1 oracle@solaris101 (MMAN) solaris101 oracle 775 222 1 oracle@solaris101 (DBW0) solaris101 oracle 777 221 1 oracle@solaris101 (LGWR) solaris101 oracle 779 220 1 oracle@solaris101 (CKPT) solaris101 oracle 781 219 1 oracle@solaris101 (SMON) solaris101 oracle 783 218 1 oracle@solaris101 (RECO) solaris101 oracle 785 217 1 oracle@solaris101 (CJQ0) solaris101 oracle 787 216 1 oracle@solaris101 (MMON) solaris101 oracle 789 215 1 oracle@solaris101 (MMNL) solaris101 oracle 884 206 4 oracle@solaris101 (q000) solaris101 oracle 793 212 1 oracle@solaris101 (ARC0) solaris101 oracle 795 211 1 oracle@solaris101 (ARC1) solaris101 oracle 797 210 7 oracle@solaris101 (QMNC) solaris101 oracle 808 213 254 oracle@solaris101 (J000) solaris101 oracle 886 205 3 oracle@solaris101 (q001) solaris101 oracle 1622 193 553 SYSTEM sqlplus@solaris101 (TNS V1-V3) solaris101 oracle 1066 201 6 SYSMAN OMS solaris101 oracle 1068 200 1 SYSMAN OMS solaris101 oracle 1070 199 6 SYSMAN OMS solaris101 oracle 1072 198 2 SYSMAN OMS solaris101 oracle 1807 197 1023 SCOTT JDBC Thin Client yohei-no-macbook-air.local yohei 1842 203 304 SYSTEM sqlplus@solaris101 (TNS V1-V3) solaris101 oracle
SPID が OS のプロセスID、SID がセッションID。