Oracle kill sessionメモ

DB（Oracle）を操作する際に クライアントからDBに接続（connect）することによって、セッションが張られる。
1つのセッションの中でトランザクションを何個も実行することになる。
（コミットまたはロールバックによって1つのトランザクションが終了し、次のトランザクションが始まる）

クライアントとDBが不慮に切断されたりすると、DB側でセッションが残り続ける（トランザクションが残り続ける。すなわちDBがロックされて、他のセッションからDBを更新できなくなる）ことがある。

そうなったセッションは強制的に削除しないと、DBの運用に支障を来す。
削除するにはsysdba（ユーザーsys）でDBに接続する必要がある。

• セッションの確認方法（v$session）
• トランザクションの確認方法（v$transaction）
• セッションとトランザクションの関係
• セッションで実行されているSQLの確認方法 [2009-03-02]
• ロックされているテーブルの調査方法 [2009-03-02]
• セッションの削除方法

稼動しているセッションを見る方法

SQL> select sid,serial#,username,logon_time from v$session;

       SID    SERIAL# USERNAME                       LOGON_TIME
---------- ---------- ------------------------------ -------------------
        16      20702 TEST                           2006/12/27 20:30:00
        20      11464 SYS                            2006/12/27 20:43:43

稼動しているトランザクションを見る方法

SQL> select ses_addr,start_time from v$transaction;

SES_ADDR         START_TIME
---------------- --------------------
000000038B2D4C70 12/27/06 21:26:47

特定のトランザクションが動いているセッションを知る方法

SQL> select sid,serial#,username,logon_time,status
  2  from v$session
  3  where saddr='000000038B2D4C70';

       SID    SERIAL# USERNAME                       LOGON_TIME STATUS
---------- ---------- ------------------------------ ---------- --------
        17       2439 TEST                           2006/12/27 ACTIVE

セッションで動いているSQL文を確認する方法

SQL文はv$sql、あるいはv$sqlareaで確認できる。[2009-03-02]
キーとなるのは、Oracle9iではADDRESS、Oracle10gではSQL_ID。

v$session上は、SQL_ADRESSおよびSQL_ID。
ただしタイミングによってはそれらの値がNULLだったり0だったりして、v$sqlとは結びつかないことがある。

select sid,serial#,username,logon_time,SQL_TEXT
from v$session s,v$sql q
where s.SQL_ID=q.SQL_ID
and SQL_FULLTEXT like'%テーブル名%'
;

select sid,serial#,username,logon_time,SQL_TEXT
from v$session s, v$sql q
where s.SQL_ADDRESS=q.ADDRESS
and SQL_TEXT like'%テーブル名%'
;

ロックされているテーブルの調査

テーブルがロックされている（と思われる）場合に、どのセッションでロックされているかを調べる方法。[2009-03-02]

select object_name,oracle_username,s.sid,s.serial#,s.logon_time,sql_address
from v$locked_object l,dba_objects o, v$session s
where l.OBJECT_ID=o.OBJECT_ID
and l.SESSION_ID=s.SID
and object_name ='テーブル名'
;

参考

• AmanojyakuさんのOracleのロックの調査と解消

特定のセッションを削除する方法

SQL> alter system kill session '17,2439';

システムが変更されました。