- PR -

セッションをkilledにするPL/SQL

1
投稿者投稿内容
ヴァンフォーレ増嶋
常連さん
会議室デビュー日: 2007/08/06
投稿数: 31
投稿日時: 2008-03-19 10:21
いつもお世話になっております。
ヴァンフォーレ増嶋です。

ユーザーをドロップして、ユーザ再作成・バックアップのdmpファイルをインポートするような処理を考えています。一応問題なく動いたのですが、ドロップ時にそのユーザに接続している場合、エラーになりドロップできません。そこでv$sessionから該当のユーザのsidとserial#を抜き出して、alter system kill sessionするPL/SQLを作っています。

なんとなく動きそうなモノができましたが、ORA-01756エラーが出てしまいます。実はSQL自体ほとんど書いたことのない初心者です。既存のスクリプトを流用して作りました。エラーの内容を見る限り超基本的なことのようですが、なかなか解決できません。（若干パニックになり、' を付けたり外したりを繰り返してます…。）

情け無い問合せで恐縮ですが、よろしくお願い致します。

[SQL]
-- ### ○○ユーザのセッションを抽出しkillする
set serveroutput on size 30000
declare
sql1 varchar2(500);
v_sid v$session%rowtype;
cursor csr is select * from v$session where username='○○ユーザ';

begin
for v_sid in csr loop
sql1 := 'alter system kill session ' || v_sid.sid || ' , ' || v_sid.serial# || ';
execute immediate sql1;
|| v_sid.serial# || '' ;'
dbms_output.put_line('｢' || v_sid.sid || '｣をKILLしました。');
dbms_output.put_line('｢' || v_sid.serial# || '｣をKILLしました。');
end loop;
end;
/

quit


[実行結果]
ERROR:
ORA-01756: 引用符付き文字列が正しく終了していません
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2008-03-19 10:36
引用:

ヴァンフォーレ増嶋さんの書き込み (2008-03-19 10:21) より:
sql1 := 'alter system kill session ' || v_sid.sid || ' , ' || v_sid.serial# || ';

ここじゃないですかね。
組み立てるべきコマンドは、
alter system kill session '10, 20'
のような形式ですよね。PL/SQL内のリテラル文字列を囲むためのシングルクォートと、リテラル文字列の一部としてのシングルクォートの区別さえしてやればよいと思います。
コード:
    sql1 := 'alter system kill session ''' || v_sid.sid || ' , ' || v_sid.serial# || '''';

こんな感じでどうでしょう?

http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/server.111/E05750-02/sql_elements.htm#42375
「リテラル内で一重引用符を表すには、一重引用符を2つ使用します。」
ヴァンフォーレ増嶋
常連さん
会議室デビュー日: 2007/08/06
投稿数: 31
投稿日時: 2008-03-19 11:40
カーニー様　さっそくのご返答ありがとうございます。

以下のように修正して再度実行しました。

begin
for v_sid in csr loop
dbms_output.put_line('｢' || v_sid.sid || '｣をKILLします。');
dbms_output.put_line('｢' || v_sid.serial# || '｣をKILLします。');
sql1 := 'alter system kill session ''' || v_sid.sid || ' , ' || v_sid.serial# || '''';
execute immediate sql1;
dbms_output.put_line('｢' || v_sid.sid || '｣をKILLしました。');
dbms_output.put_line('｢' || v_sid.serial# || '｣をKILLしました。');
end loop;
end;

｢199｣をKILLします。
｢6807｣をKILLします。
declare
*
行1でエラーが発生しました。:
ORA-00026: セッションIDが欠落しているか、または無効です。
ORA-06512: 行11

sidとserial#はひっぱってこれてきていますが、エラーになってしまいます。
原因切分けのために、生でSQLを実行してエラーメッセージを比べてみました。

<sid,serial#が存在しない場合>
ALTER SYSTEM KILL SESSION '111,111' ;

SQL> ALTER SYSTEM KILL SESSION '111,111';
ALTER SYSTEM KILL SESSION '111,111'
*
行1でエラーが発生しました。:
ORA-00030: ユーザー・セッションIDがありません。

<sid,serial#が正しく指定できていない場合①>
ALTER SYSTEM KILL SESSION '222222' ;

SQL> ALTER SYSTEM KILL SESSION '222222' ;
ALTER SYSTEM KILL SESSION '222222'
*
行1でエラーが発生しました。:
ORA-00026: セッションIDが欠落しているか、または無効です。

<sid,serial#が正しく指定できていない場合②>
ALTER SYSTEM KILL SESSION '222,,222' ;

SQL> ALTER SYSTEM KILL SESSION '222,,222' ;
ALTER SYSTEM KILL SESSION '222,,222'
*
行1でエラーが発生しました。:
ORA-00026: セッションIDが欠落しているか、または無効です。

<正常>
ALTER SYSTEM KILL SESSION '199,6807' ;

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

「ここまでくればわかるだろ！」という状態だと思いますが、まだエラーを解消できておりません。。。
カーニー
ぬし
会議室デビュー日: 2003/09/04
投稿数: 358
お住まい・勤務地: 東京
投稿日時: 2008-03-19 11:57
ん? 実際にkillするときまでにセッションが終了しているとエラーになる、と言っています?

であれば、execute immediateのみをBEGIN-EXCEPTION-ENDで囲んで、WHEN OTHERSでエラーを無視してやればよいのではないでしょうか。
ヴァンフォーレ増嶋
常連さん
会議室デビュー日: 2007/08/06
投稿数: 31
投稿日時: 2008-03-19 13:42
ヴァンフォーレ増嶋です。

｢199｣をKILLします。
｢6814｣をKILLします。
｢alter system kill session '199 , 6814'｣を実行。
declare
*
行1でエラーが発生しました。:
ORA-00026: セッションIDが欠落しているか、または無効です。
ORA-06512: 行12

SQLを表示したところ、sidとserial#の間にスペースが含まれていたため、実行できていないだけでした。スペースを削除したら実行できました！

カーニー様　>　ご指摘ありがとうございます。Excepitonはselectの条件を追加して対応いたしました。

-- ### ○○ユーザののセッションを抽出しkillする
set serveroutput on size 30000
declare
sql1 varchar2(500);
v_sid v$session%rowtype;
cursor csr is select * from v$session where username='○○ユーザ' and status <> 'KILLED';

begin
for v_sid in csr loop
sql1 := 'alter system kill session ''' || v_sid.sid || ',' || v_sid.serial# || '''';
execute immediate sql1;
end loop;
end;
/

quit
1

スキルアップ／キャリアアップ（JOB@IT）