SQL*Plus覚え書き

　SQL*Plusでは&と.で囲むと文字列を変数として扱えます。例を挙げると&hoge.という物をSQLスクリプト内に埋め込んでおくと、スクリプト実行のたびに変数への値の入力を求められる、と言うわけで。
　で、この変数、事前に値を入力しておくこともできます。僕の知っている限り、その方法は二つ。一つは比較的メジャーで、defあるいはdefineを使用して＜def hoge=8＞見たいな形で定義する方法。もう一つは結構マイナーで、SQL*Plusのcol[umn]句のnew_v[alue]を使用する方法。今回はそのマイナーな方法を使ったスクリプトを残しときます。

　このSQLを、引数にサーバプロセスのPID（HP-UXだと＜$ ps -ef |grep oracle＞なんてやると見つかります。＜$ ps -ef |grep oraclehoge＞みたいな）を与えて、systemユーザなどで実行（＜$ sqlplus system/manager@ @このスクリプト ）すると、そのサーバプロセスと対応する{
SID, serial#, クライアントPID、プログラム名}、実行しているSQL、そのセッションを切断するためのSQL文を生成します。
　シェルスクリプトに外出しすると面倒な値のやりとりも、SQL*Plusに閉じさせてしまえばラクできますよ。という一例です。ま、アプリ開発の場合、こんな再利用不可なスクリプト、共有プールを圧迫するだけの悪文なんですが、DB管理の現場では結構重宝します。

　今回は我ながら結構マニアックではないかと自賛する一品。

set pages 1000 lines 120 time on timing off
set feed off echo off trimspool on serveroutput off verify off

def v_spid = &1.

-- variable for SID
col SID     new_v v_sid
-- variable for SERIAL#
col SERIAL# new_v v_serial

select '###################################################' from dual ;
select '# Process Infomation                              #' from dual ;
select '###################################################' from dual ;
select trim(s.sid)     "SID"       ,
       trim(s.serial#) "SERIAL#'   ,
       trim(p.spid)    "SPID"      ,
       trim(s.process) "PROCESS"   ,
       trim(s.program) "PROGRAMME"
from   v$session s,
       v$process p
where  s.paddr = p.addr
and    p.spid = &v_spid.
/

select '###################################################' from dual ;
select '# SQL Infomation                                  #' from dual ;
select '###################################################' from dual ;
select p.spid     "SPID"    ,
       s.process  "PROCESS" ,
       s.program  "PROGRAM",
       a.sql_text "SQL"
from   v$sqlarea a,
       v$session s,
       v$process p
where  a.address = p.addr
and    a.hash_value = s.sql_hash_value
and    s.sid = &v_sid.
and    s.serial# = &v_serial.
and    p.spid = &v_spid.
/

select '###################################################' from dual ;
select '# SQL to kill this session                        #' from dual ;
select '###################################################' from dual ;
set head off
select 'alter system kill session ''&v_sid., &v_serial.'' ;'
from   dual
/
set head on

set timing on feed on echo on serveroutput on verify on