Oracle DB の SQL*Plus で Spool Log を取る時の定石コマンド
Oracle DB に接続するときに使う SQL*Plus。SQL*Plus の GUI ツールはちょっと使いづらいのと、改行コードの扱いで不具合があったので、普段はコマンドプロンプトから Sqlplus コマンドで使っている。
そんな Oracle の SQL*Plus で DB を覗いている時に、Spool コマンドでログを取るために予め設定しておく、ぼくなりの定石コマンドを紹介する。
目次
まずはコマンドだけ
Host Md C:\Spool\
Spool C:\Spool\Spool.log
Set time on
Set echo on
Set serverout on
Set lines 32767
Set pages 50000
Set colsep ','
Set trimspool on
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
用途・意図
仕事で Oracle を触るとき。本番環境のデータを調べたり、データパッチを行うときに、操作ログや実行 SQL を証跡として残しておくためのもの。
そんな用途のために、「情報が多く出力されること」だったり、「後々そのログファイルが追跡しやすいこと」だったり、「SELECT したデータの内容を検証・再利用しやすいこと」だったりを意識して Set コマンドを設定した。
1行ずつ説明
Host Md C:\Spool\Hostコマンドで OS のコマンドが打てるようになる。Spoolコマンドでログを出力するとき、対象のフォルダがないと失敗するので、先に Windows コマンドのMdコマンドでフォルダを作成しておく。
ちなみにMdコマンドは、C:\Hoge\フォルダがない状態でC:\Hoge\Fuga\Foo\Bar\などネストの深いパスを指定すると、そこまでのフォルダを再帰的に作成してくれる。Spool C:\Spool\Spool.log
そうして作ったフォルダにログファイルを出力し始める。Setコマンドなどよりも先にSpoolを始めているのは、どのような環境設定をしているのかを証跡としてログファイルに残すため。不要であればSetし終わってからSpoolを始めればよい。Set time onSQL*Plusのプロンプトが現在時刻になる。簡易的に実行日時を証跡に残しておくため。時刻の開きとかを見れば、作業者が操作していたときの様子も伺える。Set echo on
実行する SQL をログ出力させるため。INSERTやUPDATEの際、どんな SQL を流したか分からないと証跡にならないため。Set serverout on
PL/SQL ファイルを実行する時、同様にログ出力させるため。Set lines 32767
1行の表示領域の長さ。幅。32767 が上限値。正式名称はlinesize。SELECT結果の折り返しが発生しないようにするため。Set pages 50000
1ページに表示する行数。50000 が上限値。正式名称はpagesize。SELECT結果が長い時に区切り線を入れないようにするため。pages 0にするとヘッダなども消えてしまうため使わない。Set colsep ','
カラムの区切りをカンマにする。SELECT結果を CSV で扱おうとする時なんかに、カンマ区切りで取得しておくと使いやすい。Set trimspool on
行末のスペースを取り除く。ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
これだけSetコマンドではない。DATE 型のカラムをSELECTした時の表示形式を変更する。デフォルトだとYY-MM-DD形式とかで表示されるので、時分秒まで表示させるために記載。
以上
こんなことを考えながら Set コマンドとか書いていて、意図と合わせて周知もしているのだが、いつも別の担当者が用意する .sql ファイルの中に直接 Set echo off とか書かれてたりしてゲンナリしている。
そういやはてなブログで「SQL*Plus」と1行の中に2回書くと、アスタリスク「*」が2回登場し、Markdown 記法における強調と勘違いされてしまい、よくわからない位置で文章が強調されてしまった。
「SQL\*Plus」と、バックスラッシュ (環境によっては円記号)「\」をアスタリスクの直前に書いてエスケープしてやると回避できる。