MySQL Oracle データベース

MySQL、Oracle、PostgreSQLで実行クエリ(SQL文)の履歴(ログ)を確認する

以前に実行していたSQLを確認したい場面って、結構あるような気がします。

MySQLやshellのコマンドラインだと上や下のキーを押すことで履歴を１つずつ見れますが、過去にさかのぼって確認したい場合は、とても手間で作業効率が落ちる場合があります。

そんな時今までに実行されたクエリ（SQL文）の履歴を確認出来たら便利だと思い、自分が良く使う3つのデータベースのMySQL、PostgreSQL、Oracleに関して確認方法をまとめました。

 

MySQL

MySQLで実行クエリの履歴を確認するためには、「.mysql_history」というログファイルの中身を確認する必要があります。実行したSQLが「.mysql_history」に都度書き出されている仕様です。

ログファイルの場所はデフォルトではホームディレクトリ（/home/[ユーザ名]/）の直下にあるはずです。

vim、emacs、cat、lessなど各種コマンドで中身を確認してみてください。

注意点としては、文字コードの関係でスペースや改行が以下の文字として表示されている可能性があるので、一括置換などして見やすい形で使ってください。

  • スペース　⇒　「\040」
  • 改行(\n)　⇒　「\134n」
  • 改行(\r) 　⇒　「\134r」

 

PostgreSQL

PostgreSQLで実行クエリの履歴を確認する場合、MySQLの時と同様にログファイルがデフォルトで出力されますので、そこから確認することになります。

出力場所もホームディレクトリに「.psql_history」というファイル名で出力されているかと思います。

 

Oracle

Oracleで実行履歴を確認するには、各種ビューに対してSELECT文を発行することで確認が可能です。

Oracleのバージョン10g Release2（10gR2）以前ならV$SQLやV$SQLAREAを使い、それ以降ならV$SQLSTATSを使うのがいいかと思います。

-- 実行クエリを確認する例
SELECT * FROM V$SQLSTATS

SQL_TEXTカラムに実際発行したクエリ文が記載されています。

さらに踏み込んだ使用例として、各操作したクエリを参照したい場合は以下のように実行します。

-- 削除した履歴を確認する例
SELECT * FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'delete%';
-- 登録した履歴を確認する例
SELECT * FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'insert%';
-- 検索した履歴を確認する例
SELECT * FROM V$SQLSTATS WHERE SQL_TEXT LIKE 'select%';

さらにV$SESSIONと組み合わせることで、クエリを実行した時間帯を指定することも可能です。

SELECT 
  * 
FROM (
  SELECT 
    S.STATUS 
    , S.USERNAME 
    , NVL(S.SQL_EXEC_START, S.PREV_EXEC_START) AS TIME 
    , sq.SQL_TEXT 
  FROM 
    V$SESSION se 
  INNER JOIN 
    V$SQLSTATS sq 
  ON sq.SQL_ID(+) = NVL(S.SQL_ID, S.PREV_SQL_ID)
  ) Main 
WHERE 
  Main.TIME BETWEEN [開始日] AND [終了日]
;

注意点として、実行した際に以下のようなエラーメッセージが表示される場合は、参照する権限が付与されていない可能性があります。

行1でエラーが発生しました。:
ORA-00942: 表またはビューが存在しません。

一般ユーザでは V$SQLSTATS の参照権限がないため参照可能にするためには権限を付与する必要があります。方法としては管理ユーザでgrant文を実行することで可能になります。

grant select on V$SQLSTATS to [ユーザ名];
>権限付与が成功しました。

以上です。

-MySQL, Oracle, データベース