Oracleのセッション情報取得まとめ。SYS_CONTEXT・OSプロセスID・SQL*Plus結果のShell変数格納

Oracle Databaseの運用・調査で必要になるセッション情報の取得方法を、この記事1本にまとめて解説します。

SYS_CONTEXT で接続元IP・プログラム名などを取得する
接続しているDBユーザーのOSプロセスIDを特定する
SQL*Plusの実行結果をシェルスクリプトの変数に格納する

1. SYS_CONTEXTでセッション情報を取得

SYS_CONTEXTとは？

マニュアルには「SYS_CONTEXT は、現在のコンテキスト namespace に関連付けられた parameter の値を戻します」とあります。

かみ砕くと、Oracleには表とは別に「コンテキスト（文脈）」としてデータを持つ仕組みがあり、SYS_CONTEXT はそのコンテキストNamespaceの値を参照するためのファンクションです。実務では、事前定義済みコンテキスト USERENV からセッション情報を取得する用途がほとんどです。

接続元IPアドレスを取得

select SYS_CONTEXT('USERENV','IP_ADDRESS') from dual;

SYS_CONTEXT('USERENV','IP_ADDRESS')
--------------------------------------------------------------------------------
192.168.56.115

接続元プログラム名を取得

select SYS_CONTEXT('USERENV','MODULE') from dual;

SYS_CONTEXT('USERENV','MODULE')
--------------------------------------------------------------------------------
SQL*Plus

USERENV には他にも SESSION_USER（接続ユーザー）、SID、HOST など多数のパラメータがあります。詳細は公式マニュアルのSYS_CONTEXTを確認してください。

ロールの付与状態を確認：SYS_SESSION_ROLES

接続中のユーザーに特定のロールが付与されているかは SYS_SESSION_ROLES で確認できます。

select SYS_CONTEXT('SYS_SESSION_ROLES', 'DBA') from dual;

SYS_CONTEXT('SYS_SESSION_ROLES','DBA')
--------------------------------------------------------------------------------
TRUE

TRUE なら付与されており、FALSE なら付与されていません。

2. 接続しているDBユーザーのOSプロセスIDを取得

SYS_CONTEXT単体ではOSのプロセスIDは取得できません。v$session と v$process を結合して取得します。

select p.SPID
from v$session s, v$process p
where s.paddr = p.addr
and s.SID = sys_context('USERENV','SID');

実行例（OS側の ps と一致することを確認）:

SQL> select p.SPID from v$session s, v$process p
     where s.paddr = p.addr and s.SID = sys_context('USERENV','SID');
SPID
------------------------------------------------------------------------
9629

SQL> !ps -ef | grep oracleorcl
oracle 9629 9628 0 09:32 ? 00:00:00 oracleorcl (DESCRIPTION=(LOCAL=YES)...)

解説

v$session の SID 列と SYS_CONTEXT('USERENV','SID') は同じ値になる。自セッションの情報を取りたいときはこの条件で絞り込むのが定石
v$session は他の v$ 系ビューとも結合できる

応用：ログオントリガーでOSプロセスIDを記録

ログオン時にセッションIDとOSプロセスIDをテーブルへ記録する例です。

create table sys.os_info(
  SESSIONID NUMBER
, OS_PID    VARCHAR2(24)
, EVENTTIME DATE
);

create or replace trigger sys.get_osinfo
after logon on database
declare
  v_os_sid varchar2(24);
begin
  select p.SPID into v_os_sid
  from v$session s, v$process p
  where s.paddr = p.addr
  and s.SID = sys_context('USERENV','SID');

  insert into sys.os_info
  values(sys_context('USERENV','SESSIONID'), v_os_sid, SYSDATE);

  commit;
end;
/

3. SQL*Plusの実行結果をシェル変数に格納

運用スクリプトでSQLの結果を使いたいときは、ヒアドキュメントとコマンド置換を組み合わせます。

#!/bin/sh

variable=`sqlplus -s / as sysdba << EOF
set head off;
select sys_context('USERENV','MODULE') from dual;
exit;
EOF
`
echo ${variable}

exit 0

ポイントは次の2つです。

sqlplus -s（サイレントモード）でバナー出力を抑止する
ヒアドキュメントの終端 EOF はインデントせず左詰めで書く。インデントすると「warning: here-document delimited by end-of-file」の警告が出ます（結果自体は取得できますが、正しい書き方にしておきましょう）

まとめ

やりたいこと	方法
接続元IP・プログラム名	`SYS_CONTEXT('USERENV', 'IP_ADDRESS' / 'MODULE')`
ロール付与の確認	`SYS_CONTEXT('SYS_SESSION_ROLES', 'ロール名')`
自セッションのOSプロセスID	`v$session` × `v$process` を `SID` で絞り込み
SQL結果のシェル変数格納	`sqlplus -s` + ヒアドキュメント（EOFは左詰め）

パフォーマンス調査でセッションを特定した後の分析にはAWRまとめも参考にしてください。