カナダのInsum SolutionsがAPEX Instant Tipsというタイトルで、毎週金曜日にちょっとした小技を紹介しています。ちょうど最近"Instantly" add full table audit capability to your applicationsとして表の変更履歴を保存する方法を紹介していました。
SQL Developerを使用してジャーナル表（つまり履歴表）のDDLを生成しています。残念なことに、ライブデモでデータ・ディクショナリのインポートが成功しなかったため、ビデオを視聴しても実際の操作がわかりにくくなっています。
以下より、このビデオで触れられている、表の履歴を保存する３の方法を紹介してみます。ひとつはこのビデオの本題であるSQL Developerを使った方法、もうひとつはクイックSQLを使った方法、最後にこのビデオの視聴者からのコメントにあった、Flashback Data Archiveを使った方法です。
以下の準備作業が既に行われていることを前提とします。ワークスペース名や表EMP以外のジャーナル表を作成したい場合は、それぞれ作業内容を読み替えてください。
- Always FreeのAutonomous Transaction Processingのインスタンスがある
- APEXのワークスペースとしてAPEXDEVが作成されている
- サンプル・データセットのEMP/DEPTがインストールされている
こちらの記事にある事前準備が行われていれば、上記の条件はクリアしています。
SQL Developerを使ってジャーナル表を作成する
表EMPの履歴表（ジャーナル表）と表EMPの変更時に実行されるトリガーを生成します。
OCIのコンソールよりAutonomous Databaseのページを開き、DB接続をクリックします。ADBへの接続に使用するウォレットをダウンロードします。
開いたページにて、ウォレットのダウンロードをクリックします。
ダイアログが開くので、ダウンロードを実行します。Wallet_インスタンス名.zipというファイルがダウンロードされます。この例ではインスタンス名がAPEXDEVなので、ダウンロードされるファイル名はWallet_APEXDEV.zipになります。
パスワードの指定は必須なので適当に入力します。SQL Developerからの使用だけであれば、ウォレットに指定したパスワードが使われることはありません。
SQL Developerは以下のURLから入手できます。
https://www.oracle.com/sqldeveloper
SQL Developerを起動し（インストール作業の説明は割愛します - プラットフォームごとのInstallation Notesを参照してください）、Autonomous Databaseに接続します。
ユーザーはADMINまたはワークスペースのスキーマ（今回のワークスペース・スキーマはAPEXDEVなので、接続ユーザーとしてAPEXDEVも可）にて接続します。以下のスクリーンショットはユーザーADMINで接続しています。
接続名(Name)は任意の文字列です。以下ではadb-apexdev-adminとしています。ユーザー名としてADMIN、パスワードはADBのインスタンス作成時に指定したパスワードを指定しています。接続タイプとしてクラウド・ウォレットを選択し、構成ファイルにダウンロード済みのウォレット・ファイルを指定します。Always Freeのインスタンスであれば、サービスはインスタンス名_lowを選びます。以上の入力を行い、接続をクリックします。
データベースに接続されたら、データ・モデラーにジャーナル表を生成する対象である表EMPの定義を読み込みます。
ファイル・メニューより、Data Modeler、インポート、データ・ディクショナリを呼び出します。
接続名を選択し、次へ進みます。
ジャーナル表を作成したい表を含むスキーマを選択します。今回の例では表EMPを含んでいるのはスキーマAPEXDEVなので、APEXDEVにチェックを入れています。次へ進みます。
ジャーナル表の生成対象となる表を選択します。今回の例ではEMPです。次へ進みます。
終了をクリックし、表EMPの定義をデータ・モデラーへインポートします。
インポートのログを保存するかどうか確認されます。今回は不要なので、閉じるをクリックします。
表EMPの定義がインポートされました。
続けて、たった今インポートした表EMPの定義をエクスポートします。エクスポートする際に、ジャーナル表とそれを操作するトリガーのDDLを生成します。
ファイル・メニューより、Data Modeler、エクスポート、DDLファイルを呼び出します。
ダイアログが開くので、生成をクリックします。
表のDDLスクリプトを含めるのタブを開き、対象となる表（この例ではAPEXDEV.EMP）にチェックを入れます。OKをクリックします。
ここでアクティブなスクリプト・セットとしてjournal tablesが選択されています。この指定により、ジャーナル表とトリガーのDDLが生成されます。
DDLが生成され、画面に表示されます。表名の末尾に_JNが付加された表がジャーナル表、_JNtrgが付加されたトリガーがジャーナル表へ表EMPの変更を書き込むトリガーになります。
生成されたDDLスクリプトをファイルに保存するか、必要な部分をコピー＆ペーストして保存します。
以下が生成されたDDLの例です。
この中でCREATE TABLE APEXDEV.EMP_JNのDDLとCREATE OR REPLACE TRIGGER APEXDEV.EMP_JNtrgがジャーナル表に関係します。
データベース・アクションのSQLまたはSQLワークショップのSQLコマンドから、CREATE TABLE文およびCREATE TRIGGER文を実行すると、ジャーナル表およびジャーナル表への変更の書き込みが行われるようになります。
CREATE TABLE文の実行です。
CREATE OR REPLACE TRIGGER文の実行です。
アクティブなスクリプト・セットとして指定されている
journal tablesは、
ツール・メニューの
Data Modeler、
設計ルールおよび変換、
表DDL変換にて処理内容が定義されています。
表のDDL変換スクリプトとしてjournal tablesが定義されています。そこに記載されているJavaScriptのコードによって、ジャーナル表のCREATE TABLE文やトリガーのCREATE TRIGGER文が生成されていることが確認できます。
_JNや_JNtrgといったポストフィックスを変更したい場合、および、ジャーナル表に追加されるカラムを変更したい、といった場合は生成されたDDLを編集するか、または、ここに記載されているJavaScriptを編集することによって対応できるでしょう。
クイックSQLを使って履歴表を作成する
クイックSQLの表ディレクティブとしてhistoryがあります。ただし、この機能はOracle APEX 21.1よりデサポートになっています。
https://docs.oracle.com/en/database/oracle/application-express/21.1/htmrn/index.html#HTMRN-GUID-4B10102D-0984-4C17-A1F2-6F8B1343D757
7.1.8 History Table in Quick SQL Desupported
Quick SQL no longer generates a history table nor a trigger to log table data changes.
どのような機能なのか、以下に紹介します。LiveSQLに含まれるQuick SQLでは使用できます。
表EMPの履歴を取るには、表ディレクティブとしてhistoryを指定します。表EMPを例にとると、クイックSQLの定義は以下になります。
# genpk: false
# semantics: default
emp /history
empno num
ename vc50
job vc50
mgr num
hiredate date
sal num
comm num
deptno num
表EMPの指定に続いて/historyを記述します。
生成されるSQLは以下になります。
変更履歴を保存する表はHISTORYひとつのみです。それぞれの表への変更と削除（挿入は除く）の操作は、表ごとに作成されたトリガーにより表HISTORYへ記載されます。
表ごとに履歴表（ジャーナル表）を作成していないため、表への変更（列の追加など）の対応がトリガーの変更のみで済むといった利点があります。ただし、保存された履歴データの利用は困難で、このような記録の仕方であれば統合監査により監査証跡を取得した方が、トリガーの定義も不要でパフォーマンス面でのデメリットも回避できるかと思います。
Flashback Data Archiveを構成する
マニュアルの
Oracle Flashbackの制限事項として記載がありますが、Autonomous DatabaseではFlashback Data Archiveを構成することはできません。そのため、Oracle Database 18c Express Editionを使用して動作を確認してみました。
2023年8月追記：
Flashback Time Travel（Flashback Data Archiveの新しい名称 - 機能は同じ）がAutonomous Databaseで利用できるようになりました。ただし、ユーザー・コンテキストの保存はできないようです。
Flashback Data Archiveについては、データベース開発ガイドの
フラッシュバック・アーカイブの使用にて説明されています。これからの説明は主にConnor McDonaldさんによるYouTubeのビデオを参照しています。
Flashback Data ArchiveFlashback Data Archive ... can it handle high volume of transactions?
https://www.youtube.com/watch?v=FpRAc-FEWbE(
5:14以降よりFDAの障害についていくつか説明があります)
以下はSYSで作業しています。
Flashback Data Archiveを作成します。すでに表領域USERSが作成済みで、そこにFlashback Data Archiveの領域を1Gバイト確保します。保持期間は１日に設定します。通常はもっと長い期間を設定するでしょう。
create flashback archive default longterm
tablespace users quota 1g retention 1 day;
スキーマAPEXDEVにすでに存在している表EMPにたいして、Flashback Data Archiveを有効にします。
alter table apexdev.emp flashback archive longterm;
Flashback Data Archiveのコンテキスト・レベルをALLに変更します。
DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVELを呼び出します。Oracle APEXでは、アプリケーション・コンテキストAPEX$SESSIONにAPP_ID、APP_SESSION、APP_USERといった値が保持されています。コンテキスト・レベルをALLにすると、APEX$SESSIONが保存の対象になります。（同様の情報はビューSYS_FBA_CONTEXT_AUDの列MODULEまたはCLIENT_IDENTIFIERからも参照できるため、この設定は必須ではありません）。
begin
dbms_flashback_archive.set_context_level(
level => 'ALL'
);
end;
/
SQLワークショップのSQLコマンドより実行します。
select empno, ename, job, mgr, sal, comm, deptno
, versions_starttime, versions_endtime, versions_xid, versions_operation
, dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION', 'APP_ID') app_id
, dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION', 'APP_USER') app_user
from emp
versions between timestamp
timestamp'2021-08-30 15:30:00' and systimestamp
where versions_xid is not null
order by versions_starttime asc;
列EMPNO、ENAMEといった列の値は、VERSIONS_STARTTIMEからVERSIONS_ENDTIMEまでの間維持されていたこと、この列はVERSIONS_OPERATIONでの操作により変更されたこと、アプリケーションIDが100であるAPEXアプリケーションより操作が行われたこと、APEXのユーザーADMINによって操作が行われたことなどを確認することができます。
ビューSYS_FBA_CONTEXT_AUDと列XIDでジョインをすると、CLIENT_IDENTIFIERやMODULEの情報も参照することができます。
select empno, ename, job, mgr, sal, comm, deptno
, a.client_identifier, a.module
from
(
select
empno, ename, job, mgr, sal, comm, deptno
, versions_xid
from emp
versions between timestamp
timestamp'2021-08-30 14:20:00' and systimestamp
) e, sys.sys_fba_context_aud a
where e.versions_xid = a.xid
最近のRU(Release Update)には、FDAのパフォーマンスを改善を行なう修正が入っているとのことなので、利用を検討される際には最新のRU を確認することをお勧めします。
表の変更履歴の取得にも色々な方法があり、それぞれ一長一短があります。要件に応じて方法を決めるのが良いかと思います。
以上になります。Oracle APEXのアプリケーション開発の参考になれば幸いです。
完
