[Oracle] SQLスクリプトの中で特定の条件を満たしている場合にのみ再起動をかける

動的変更不可の初期化パラメータを変更する場合、インスタンスの再起動が必要になります。

これを SQL スクリプトの中で実現しようと思ったら、以下のような感じになるかと思います。

connect / as sysdba

ALTER SYSTEM SET UTL_FILE_DIR=* SCOPE=SPFILE;

shutdown immediate
startup

exit

ただし、上記の例のように無条件に「設定変更」→「再起動」というふうにすると、既に設定が変更されていたとしても必ず再起動がかけられてしまいます。


そこで、既に設定が変更されている場合は再起動がかからないようにするにはどうすればいいかを考えてみました。


まず、最初に考えたのは一連の処理を PL/SQL ブロック化して IF 文などの条件分岐を使用する方法です。

connect / as sysdba

DECLARE
	wk_count PLS_INTEGER;
BEGIN
	SELECT COUNT(*) INTO wk_count FROM V$PARAMETER WHERE NAME = 'utl_file_dir' AND VALUE = '*';
	IF wk_count = 1 THEN
		EXECUTE IMMEDIATE 'ALTER SYSTEM SET UTL_FILE_DIR=* SCOPE=SPFILE';
		EXECUTE IMMEDIATE 'shutdown immediate';
		EXECUTE IMMEDIATE 'startup';
	END IF;
END;
/

exit

ところが、残念ながら shutdown コマンド、startup コマンドは SQL*Plus のコマンドであるため PL/SQL ブロックの中では使用できません。

そのため、上記の SQL スクリプトを実行すると、shutdown コマンドの実行時にエラー（ORA-00900: SQL文が無効です。）が発生してしまいます。


次に考えたのは設定確認用の SQL スクリプトと設定用の SQL スクリプトを分け、呼び出し元（シェルやバッチファイルなど）で設定確認用の SQL スクリプトの実行結果に応じて、設定用の SQL スクリプトを実行するかどうかを判定する方法です。

connect / as sysdba

variable cnt number;
BEGIN
	SELECT COUNT(*) INTO :cnt FROM V$PARAMETER WHERE NAME = 'utl_file_dir' AND VALUE = '*';
END;
/

exit :cnt

上記のような設定確認用の SQL スクリプトを実行すると、設定が変更済であれば 1 が、未変更であれば 0 が戻り値として返りますので、呼び出し元でその戻り値を判定してやればOKです。


あと、少し無理矢理感がありますが whenever コマンドを使用する方法も考えられます。

connect / as sysdba

whenever sqlerror exit

SELECT 1 / (COUNT(*) - 1) FROM V$PARAMETER WHERE NAME = 'utl_file_dir' AND value = '*';

ALTER SYSTEM SET UTL_FILE_DIR=* SCOPE=SPFILE;

shutdown immediate
startup

exit

上記のような SQL スクリプトを実行すると、設定が変更済みの場合にエラー（ORA-01476: 除数がゼロです。）が発生し、そこで SQL スクリプトが終了するため、設定変更とインスタンスの再起動が実行されません。

1つの SQL スクリプトの中で済ましてしまいたい場合に有効です。