以前に表の操作ログを取得する方法を紹介する記事（表の操作ログを取得するおよび追記）を書いています。その中でFlashback Data Archive（現在の名称はFlashback Time Travel）を紹介しています。当時Autonomous Databaseでは、この機能は利用できませんでした。
最近、Autonomous DatabaseでFlashback Time Travelが利用できるようになりました。プロダクト・マネージャのCan Tuzlaさんによる紹介記事が公開されています。How to Use Flashback Time Travel in Autonomous Database
ドキュメントの記載は以下です。利用にあたって制限事項があるので、それは要確認です。
Tracking Table Changes with Flashback Time Travel
なお、この機能はOracle Total Recallと呼ばれていたこともあるようです。
上記のドキュメントにはできるともできないとも書いてありませんが、Autonomous DatabaseのFlashback Time Travelでは、アプリケーション・コンテキストの保存はサポートしていないようです。DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVELの呼び出しは失敗します。APEXではアプリケーション・コンテキストAPEX$SESSIONに、サインインしているユーザーやアプリケーションIDなどが保存されているので、それをFlashback Time Travelで扱えないのは少々残念です。
しかし、APEXを使っていても使っていなくても、表の変更履歴を取得するという要件に対しては、Flashback Time Travelを使うことがお勧めです。履歴を保存するための表を作成したり、コードを書く必要はありません。
以下のように動作する、簡単なAPEXアプリケーションを作成します。
Flashback Time Travelを有効にした表を作成し、その表のデータの変更履歴を一覧するページを作成します。
Autonomous Databaseの場合、Flashback Time Travelはデフォルトで有効になっています。状態を確認してみます。
SQL Developer Webに管理者ユーザーADMINで接続し、以下のSQLを実行します。
select * from DBA_FLASHBACK_ARCHIVE
ADBではフラッシュバック・アーカイブとしてFLASHBACK_ARCHIVEが作成されます。追加や削除はできません。
フラッシュバック・アーカイブが作成されている表領域を確認します。
select * from DBA_FLASHBACK_ARCHIVE_TS
表領域DATAに作成されていて、クオータはNULL、つまり無制限になっています。
これからAPEXのアプリケーションを作成します。表に対してFlashback Time Travelを有効にする作業をAPEXで行うには、ワークスペース・スキーマに以下の権限を与える必要があります。
表の変更履歴の取得は監査の意味を持つこともあるため、開発者が自由に有効にしたり無効にしたりする運用は考えにくいです。そのため、上記の権限をAPEXのワークスペース・スキーマに与えることはせず、表に対してFlashback Time Travelを有効化する作業は管理者ユーザーのADMINによって行うことにします。
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBERをAPEXのワークスペース・スキーマで呼び出せるように、パッケージDBMS_FLASHBACKの実行権限をAPEXのワークスペース・スキーマに与えます。
grant execute on dbms_flashback to <APEXワークスペース・スキーマ>;
クイックSQLを使って、今回のAPEXアプリケーションで使用する表FTT_TESTを作成します。以下のモデルを使用します。
# prefix: ftt
# auditcols: true
test
name vc80 /nn
salary numSQLの生成、SQLスクリプトを保存、レビューおよび実行を順次クリックします。
DDLを実行します。確認画面が開くので、即時実行します。
表FTT_TESTが作成されました。
アプリケーション作成ウィザードを起動します。
作成するアプリケーションの名前は変更履歴の確認とします。デフォルトで作成されるホーム・ページは削除し、代わりに表FTT_TESTをソースとした対話グリッドのページと、変更履歴を表示する対話モード・レポートのページを追加します。
対話グリッドのページは、以下の設定で作成します。
ページ名は編集とし、編集を許可します。表またはビューとしてFTT_TESTを指定します。
対話モード・レポートのページは、以下の設定で作成します。
ページ名は変更履歴、SQL問合せを選択し、以下のSQLを記述します。
以上のページを追加したのち、アプリケーションの作成をクリックします。
アプリケーション変更履歴の確認が作成されます。
SQL Developer Webに戻り、表FTT_TESTにたいしてFlashback Time Travelを有効にします。
select * from dba_flashback_archive_tables;
APEXのアプリケーションに戻ります。
対話グリッドのページを開き、監査列のCREATED、CREATED_BY、UPDATED、UPDATED_BYを選択します。
識別のタイプを表示のみに変更し、ソースの問合せのみをオンにします。
タイプを非表示から表示のみに変更しているため、ヘッダーが未設定になっています。
列CREATEDのヘッダーにCreatedを設定します。同様に列CREATED_BYのラベルとしてCreated By、列UPDATEDのラベルとしてUpdated、列UPDATED_BYのラベルとしてUpdated Byを設定します。
対話グリッドのページの変更は以上です。
続いて変更履歴を表示する対話モード・レポートのページを変更します。
現在のSCNとタイムスタンプを表示するリージョンを作成します。
識別のタイトルは現在、タイプにクラシック・レポートを選択します。ソースのタイプにSQL問合せを選択し、SQL問合せとして以下を記述します。
select dbms_flashback.get_system_change_number current_scn, systimestamp from dual
変更履歴を検索する期間を指定するページ・アイテムを作成します。
識別の名前はP2_MINUTES、タイプは数値フィールド、ラベルは分前とします。検証の必須の値をオンにします。デフォルトのタイプに静的を選び、静的値として10を設定します。
ページ・アイテムP2_MINUTSに動的アクションを作成します。値が変更された時に、変更履歴の表示をリフレッシュします。
識別の名前はonChange Refreshとします。タイミングのイベントはデフォルトの変更です。
TRUEアクションとしてリフレッシュを選択します。影響を受ける要素の選択タイプとしてリージョン、リージョンとして変更履歴を選択します。
対話モード・レポート変更履歴のソースのSQL問合せに含まれるファンクションnumtodsintervalの第１引数を固定値の１からページ・アイテム:P2_MINUTESに変更します。
送信するページ・アイテムとしてP2_MINUTESを設定します。
タイムスタンプの表示をできるだけ詳細にするため、アプリケーション・レベルでフォーマットを設定します。
アプリケーション定義のグローバリゼーションを開きます。
アプリケーションのタイムスタンプ書式にYYYY/MM/DD HH24:MI:SSXFF、タイムスタンプ・タイムゾーン書式にYYYY/MM/DD HH24:MI:SSXFF TZRを設定します。
以上でアプリケーションは完成です。アプリケーションを実行すると、記事の先頭のGIF動画のように動作します。
変更履歴の保存期間は、DBMS_CLOUD_ADMIN.SETE_FLASHBACK_ARCHIVE_RETENTIONを呼び出して変更します。変更結果は、ビューDBA_FLASHBACK_ARCHIVEの列RETENTION_IN_DAYSから確認できます。
BEGIN
DBMS_CLOUD_ADMIN.SET_FLASHBACK_ARCHIVE_RETENTION
(retention_days => 365);
END;
/
変更履歴のパージは、DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVEを呼び出してパージします。scopeはALL、SCN、TIMESTAMPを選択でき、SCNの場合はbefore_scn、TIMESTAMPの場合はbefore_timestampを指定することにより、ある時点より古い変更履歴に限定してパージできます。
scopeにALLを指定して、変更履歴をパージしてみます。
BEGIN
DBMS_CLOUD_ADMIN.PURGE_FLASHBACK_ARCHIVE
(scope => 'ALL');
END;
/
この現象の理由として、Flashback Version Queryは、Flashback ArchiveだけではなくUNDO領域も参照しているためか、または、履歴の保存に（パフォーマンスを維持するため）パーティション表を使っているとしたら、最低１個はパーティションを削除せずに残すためではないか、とのことでした。
どちらにしても、scopeをALLでパージを行なったときに直近の変更履歴が消えない点は、問題とは扱われません。そもそも、通常の運用でscopeをALLで変更履歴を全削除することはないでしょう。
今回作成したAPEXアプリケーションのエクスポートを以下に置きました。
https://github.com/ujnak/apexapps/blob/master/exports/flashback-time-travel-sample.zip
Oracle APEXのアプリケーション作成の参考になれば幸いです。
完
