Oracle Database 11g Release 2 から利用できる DDL のロギング機能について試してみました。
DDL のロギング機能
データベースに対して発行されたオブジェクトの作成や変更等の SQL 文を DDL （マニュアル上は「データ定義言語」「Data Definition Language」）と呼びます。
Oracle Database には DDL が発行されると、実行された DDL をログに記録する機能があります。Oracle Database 11g まではアラート・ファイルに書き込まれましたが、Oracle Database 12c 以降は ADR 内の独立したファイルとして作成されます。
設定変更方法
初期化パラメーター enable_ddl_logging を TRUE に変更します（デフォルト値は FALSE）。
SQL> ALTER SYSTEM SET enable_ddl_logging = TRUE;
System altered.
出力されるログ
DDL はファイルに出力されます。ADR内の下記のファイルに出力されます。ファイルが存在しなければ ALTER SYSTEM 文を実行されると自動的に作成されます。
- ADR_HOME/log/ddl_{インスタンス名}.log ファイル
このファイルはテキストファイルです。実行された日時と DDL 文が２行に分かれて出力されます。
$ cd $ORACLE_BASE/diag/rdbms/o18a/O18A/log
$ cat ddl_O18A.log
2018-08-15T11:23:34.671263+09:00
diag_adl:CREATE TABLE ddltest1(c1 NUMBER, c2 VARCHAR2(10))
$
- ADR_HOME/log/ddl/log.xml ファイル
このファイルは XML 形式で出力されます。
$ cd $ORACLE_BASE/diag/rdbms/o18a/O18A/log/ddl
$ cat log.xml
<msg time='2018-08-15T11:23:34.670+09:00' org_id='oracle' comp_id='rdbms'
msg_id='opiexe:4792:2946163730' type='UNKNOWN' group='diag_adl'
level='16' host_id='rel75-1' host_addr='fe80::1465:9426:ef2a:9304%ens33'
pid='28637' version='1'>
<txt>CREATE TABLE ddltest1(c1 NUMBER, c2 VARCHAR2(10))
</txt>
</msg>
何に使うのか？
この機能は一見便利ですが、下記のような仕様から何が目的で作られた機能なのかがはっきりせず、微妙な実装になっています。
- すべての DDL が出力されるわけではありません
CREATE USER文、CREATE PUBLIC SYNONYM 文、CREATE PUBLIC DATABASE LINK 文などは出力の対象外です。対象 DDL は マニュアル に記載されています。ただし 対象の DDL であれば SYS ユーザーが実行した場合でも出力されます。
- 誰が実行したかが出力されません
DDL を実行したユーザー名が出力されません。このため DDL からどのユーザーが作成したオブジェクトなのかを特定できません。
- スキーマ名が出力されません
上記と同様にスキーマ名（ユーザー名）が出力されないため、CREATE TABLE 文が出力されてもどのユーザーが所有しているかわかりません。
- 完全な DDL が出力されるわけではありません。
マニュアルにも明記されていますが、長すぎる DDL は途中でカットされます。DDL 全体を取得するためには DDL トリガーを使う必要があります。
- 監査に使えません
初期化パラメーター enable_ddl_logging は、ALTER SESSION システム権限を持つ一般ユーザーも変更できます。このため監査データとして使うには無理があります。