セッションを一覧表示する

v$sessionは情報が多すぎるので用途によってクエリを使い分けると効果的。

1.接続元とログイン時間を中心に

SQL> 
set pages 10000
set lines 150
COLUMN USERNAME FORMAT a20
COLUMN STATUS FORMAT a10
COLUMN EVENT FORMAT a60
COLUMN PROGRAM FORMAT a50
COLUMN SID FORMAT 99999
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select sid,serial#,program,username,logon_time,status
from v$session order by logon_time;

   SID    SERIAL# PROGRAM                                       USERNAME   LOGON_TIME          STATUS
------ ---------- --------------------------------------------- ---------- ------------------- ----------
   547          1 oracle@snwdb0.smc.indexweb.co.jp (RECO)                  2011/02/07 12:57:00 ACTIVE
   554          1 oracle@snwdb0.smc.indexweb.co.jp (PSP0)                  2011/02/07 12:57:00 ACTIVE
   553          1 oracle@snwdb0.smc.indexweb.co.jp (MMAN)                  2011/02/07 12:57:00 ACTIVE
   552          1 oracle@snwdb0.smc.indexweb.co.jp (DBW0)                  2011/02/07 12:57:00 ACTIVE
   544          1 oracle@snwdb0.smc.indexweb.co.jp (MMNL)                  2011/02/07 12:57:00 ACTIVE
   545          1 oracle@snwdb0.smc.indexweb.co.jp (MMON)                  2011/02/07 12:57:00 ACTIVE
   546          1 oracle@snwdb0.smc.indexweb.co.jp (CJQ0)                  2011/02/07 12:57:00 ACTIVE
   555          1 oracle@snwdb0.smc.indexweb.co.jp (PMON)                  2011/02/07 12:57:00 ACTIVE
   548          1 oracle@snwdb0.smc.indexweb.co.jp (SMON)                  2011/02/07 12:57:00 ACTIVE
   549          1 oracle@snwdb0.smc.indexweb.co.jp (CKPT)                  2011/02/07 12:57:00 ACTIVE
   550          1 oracle@snwdb0.smc.indexweb.co.jp (LGWR)                  2011/02/07 12:57:00 ACTIVE
   551          1 oracle@snwdb0.smc.indexweb.co.jp (DBW1)                  2011/02/07 12:57:00 ACTIVE
   540          7 oracle@snwdb0.smc.indexweb.co.jp (QMNC)                  2011/02/07 12:57:10 ACTIVE
   537          2 oracle@snwdb0.smc.indexweb.co.jp (q000)                  2011/02/07 12:57:20 ACTIVE
   536          4 oracle@snwdb0.smc.indexweb.co.jp (q001)                  2011/02/07 12:57:20 ACTIVE
   527      19899 sqlplus@codev0.smc.indexweb.co.jp (TNS V1-V3) PERFSTAT   2011/02/07 23:23:39 ACTIVE

2.実行中のイベントを中心に

SQL> 
set pages 10000
set lines 120
COLUMN USERNAME FORMAT a20
COLUMN STATUS FORMAT a10
COLUMN EVENT FORMAT a60
COLUMN SID FORMAT 99999
alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
select sid,serial#,username,event from v$session order by logon_time;

   SID   SERIAL# USERNAME   EVENT
------ ---------- ---------- ------------------------------------------------------------
   547         1            rdbms ipc message
   554         1            rdbms ipc message
   553         1            rdbms ipc message
   552         1            rdbms ipc message
   544         1            rdbms ipc message
   545         1            rdbms ipc message
   546         1            rdbms ipc message
   555         1            pmon timer
   548         1            smon timer
   549         1            rdbms ipc message
   550         1            rdbms ipc message
   551         1            rdbms ipc message
   540         7            Streams AQ: qmn coordinator idle wait
   537         2            Streams AQ: waiting for time management or cleanup tasks
   536         4            Streams AQ: qmn slave idle wait
   527     19899 PERFSTAT   SQL*Net message to client