sqlplusで実行したSQLをbashで書き出す方法

2020.10.27 (火)

Oracleは最初から最後まで手のかかる代物でうんざりです。
sqlplusで実行したSQLの結果をファイルに書き出す方法です。結果としてspoolは出力した内容の加工ができないので、正直使い勝手がよくないので「spool/spool off」は使わない方が無難。しかしただSQLの内容をドバっと吐き出すだけだったら有用。

プロセス数とセッション数を1分おきに書き出す方法です。
SQLでいうとこんな感じになります。

SELECT RESOURCE_NAME,CURRENT_UTILIZATION FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME = 'processes'
OR RESOURCE_NAME = 'sessions';

結果は、

RESOURCE_NAME CURRENT_UTILIZATION
----------------------------------
processes     742
sessions      786   

といたってシンプルです。

SQL文の標準出力する

このSQLを標準出力に出してみるには、

#!/bin/bash

sqlplus -s / as sysdba <<'EOF'
SELECT RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME = 'processes'
OR RESOURCE_NAME = 'sessions';
EOF

exit

となります。bashのヒアドキュメントにSQL文を渡して実行してあげます。ややこしいのがヒアドキュメントのエスケープで、ヒアドキュメント内では$がシェルの変数として展開されてしまうので、ここでは’EOF’として$が展開されないようにしています。<<EOFで設定した際には、V$RESOURCE_LIMITテーブルはV\$RESOURCE_LIMITというふうにバックスラッシュで$を直接エスケープします。
SQL文の中に変数パラメータを渡したいときはなかなか便利ですが、$付きのオラクルテーブルなどを扱うときは注意が必要です。

SQL文の結果を変数に格納する

バッククォートで囲います。RESULTに結果を突っ込んでおけばあとは好きなように加工すればOKです。

#!/bin/bash

RESULT=`sqlplus -s / as sysdba <<'EOF'
SELECT RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,LIMIT_VALUE FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME = 'processes'
OR RESOURCE_NAME = 'sessions';
EOF
`
echo $RESULT
exit

SQL文の結果をファイルに書き出す(リダイレクト)

ファイルへの書き出しは、echoから>>(リダイレクト)で書き出せばいいです。

...
echo $RESULT >> somefile.txt
...

ここまではOKです。

cronで実行する

cronで実行するとbashのコマンドはうまく動作するけど、オラクルのSQLの中身が全然動作しないことがあります。というのもcronはオラクルの環境変数を横こまないので環境変数を使っているsqlplusが実行されないというだけです。なのでbashのコマンドに--loginまたは-lを与えてあげればOKです。
(もっと繊細にやるべき場合は、cron自体に環境変数をsetしておくのがよいと思います。)

15 * * * * /bin/bash -l /home/oracle/test-sqlplus.sh > /dev/null 2>&1

みたいな感じにすると、きちんと実行されます。