2021年8月30日月曜日

表の操作ログを取得する - 追記

 カナダのInsum SolutionsがAPEX Instant Tipsというタイトルで、毎週金曜日にちょっとした小技を紹介しています。ちょうど最近"Instantly" add full table audit capability to your applicationsとして表の変更履歴を保存する方法を紹介していました。

SQL Developerを使用してジャーナル表（つまり履歴表）のDDLを生成しています。残念なことに、ライブデモでデータ・ディクショナリのインポートが成功しなかったため、ビデオを視聴しても実際の操作がわかりにくくなっています。

以下より、このビデオで触れられている、表の履歴を保存する３の方法を紹介してみます。ひとつはこのビデオの本題であるSQL Developerを使った方法、もうひとつはクイックSQLを使った方法、最後にこのビデオの視聴者からのコメントにあった、Flashback Data Archiveを使った方法です。

以下の準備作業が既に行われていることを前提とします。ワークスペース名や表EMP以外のジャーナル表を作成したい場合は、それぞれ作業内容を読み替えてください。

  1. Always FreeのAutonomous Transaction Processingのインスタンスがある
  2. APEXのワークスペースとしてAPEXDEVが作成されている
  3. サンプル・データセットの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がジャーナル表に関係します。

-- 生成者 Oracle SQL Developer Data Modeler 21.2.0.165.1515
-- 時刻: 2021-08-30 11:00:48 JST
-- サイト: Oracle Database 12cR2
-- タイプ: Oracle Database 12cR2
CREATE TABLESPACE data
-- WARNING: Tablespace has no data files defined
LOGGING ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
FLASHBACK ON;
CREATE USER apexdev IDENTIFIED BY ACCOUNT UNLOCK ;
-- predefined type, no DDL - MDSYS.SDO_GEOMETRY
-- predefined type, no DDL - XMLTYPE
CREATE TABLE apexdev.emp (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(50 BYTE),
job VARCHAR2(50 BYTE),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2)
)
PCTFREE 10 PCTUSED 40 INITRANS 10 TABLESPACE data LOGGING
STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT )
NO INMEMORY;
CREATE INDEX apexdev.emp_1 ON
apexdev.emp (
mgr
ASC )
TABLESPACE data PCTFREE 10 INITRANS 20
STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT )
LOGGING;
CREATE INDEX apexdev.emp_2 ON
apexdev.emp (
deptno
ASC )
TABLESPACE data PCTFREE 10 INITRANS 20
STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT )
LOGGING;
CREATE UNIQUE INDEX apexdev.emp_pk ON
apexdev.emp (
empno
ASC )
TABLESPACE data PCTFREE 10 INITRANS 20
STORAGE ( INITIAL 65536 NEXT 1048576 PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 2147483645 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT )
LOGGING;
ALTER TABLE apexdev.emp
ADD CONSTRAINT emp_pk PRIMARY KEY ( empno )
USING INDEX apexdev.emp_pk;
CREATE TABLE APEXDEV.EMP_JN
(JN_OPERATION CHAR(3) NOT NULL
,JN_ORACLE_USER VARCHAR2(30) NOT NULL
,JN_DATETIME DATE NOT NULL
,JN_NOTES VARCHAR2(240)
,JN_APPLN VARCHAR2(35)
,JN_SESSION NUMBER(38)
,EMPNO NUMBER (4) NOT NULL
,ENAME VARCHAR2 (50 BYTE)
,JOB VARCHAR2 (50 BYTE)
,MGR NUMBER (4)
,HIREDATE DATE
,SAL NUMBER (7,2)
,COMM NUMBER (7,2)
,DEPTNO NUMBER (2)
);
CREATE OR REPLACE TRIGGER APEXDEV.EMP_JNtrg
AFTER
INSERT OR
UPDATE OR
DELETE ON APEXDEV.EMP for each row
Declare
rec APEXDEV.EMP_JN%ROWTYPE;
blank APEXDEV.EMP_JN%ROWTYPE;
BEGIN
rec := blank;
IF INSERTING OR UPDATING THEN
rec.EMPNO := :NEW.EMPNO;
rec.ENAME := :NEW.ENAME;
rec.JOB := :NEW.JOB;
rec.MGR := :NEW.MGR;
rec.HIREDATE := :NEW.HIREDATE;
rec.SAL := :NEW.SAL;
rec.COMM := :NEW.COMM;
rec.DEPTNO := :NEW.DEPTNO;
rec.JN_DATETIME := SYSDATE;
rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
IF INSERTING THEN
rec.JN_OPERATION := 'INS';
ELSIF UPDATING THEN
rec.JN_OPERATION := 'UPD';
END IF;
ELSIF DELETING THEN
rec.EMPNO := :OLD.EMPNO;
rec.ENAME := :OLD.ENAME;
rec.JOB := :OLD.JOB;
rec.MGR := :OLD.MGR;
rec.HIREDATE := :OLD.HIREDATE;
rec.SAL := :OLD.SAL;
rec.COMM := :OLD.COMM;
rec.DEPTNO := :OLD.DEPTNO;
rec.JN_DATETIME := SYSDATE;
rec.JN_ORACLE_USER := SYS_CONTEXT ('USERENV', 'SESSION_USER');
rec.JN_APPLN := SYS_CONTEXT ('USERENV', 'MODULE');
rec.JN_SESSION := SYS_CONTEXT ('USERENV', 'SESSIONID');
rec.JN_OPERATION := 'DEL';
END IF;
INSERT into APEXDEV.EMP_JN VALUES rec;
END;
/ALTER TABLE apexdev.emp
ADD CONSTRAINT emp_dept_fk FOREIGN KEY ( deptno )
REFERENCES apexdev.dept ( deptno )
NOT DEFERRABLE;
ALTER TABLE apexdev.emp
ADD CONSTRAINT emp_mgr_fk FOREIGN KEY ( mgr )
REFERENCES apexdev.emp ( empno )
NOT DEFERRABLE;
CREATE SEQUENCE apexdev.emp_seq START WITH 1 NOCACHE ORDER;
CREATE OR REPLACE TRIGGER apexdev.emp_trg1 BEFORE
INSERT ON apexdev.emp
FOR EACH ROW
WHEN ( new.empno IS NULL )
BEGIN
:new.empno := apexdev.emp_seq.nextval;
END;
/
-- Oracle SQL Developer Data Modelerサマリー・レポート:
--
-- CREATE TABLE 1
-- CREATE INDEX 3
-- ALTER TABLE 3
-- CREATE VIEW 0
-- ALTER VIEW 0
-- CREATE PACKAGE 0
-- CREATE PACKAGE BODY 0
-- CREATE PROCEDURE 0
-- CREATE FUNCTION 0
-- CREATE TRIGGER 1
-- ALTER TRIGGER 0
-- CREATE COLLECTION TYPE 0
-- CREATE STRUCTURED TYPE 0
-- CREATE STRUCTURED TYPE BODY 0
-- CREATE CLUSTER 0
-- CREATE CONTEXT 0
-- CREATE DATABASE 0
-- CREATE DIMENSION 0
-- CREATE DIRECTORY 0
-- CREATE DISK GROUP 0
-- CREATE ROLE 0
-- CREATE ROLLBACK SEGMENT 0
-- CREATE SEQUENCE 1
-- CREATE MATERIALIZED VIEW 0
-- CREATE MATERIALIZED VIEW LOG 0
-- CREATE SYNONYM 0
-- CREATE TABLESPACE 1
-- CREATE USER 1
--
-- DROP TABLESPACE 0
-- DROP DATABASE 0
--
-- REDACTION POLICY 0
--
-- ORDS DROP SCHEMA 0
-- ORDS ENABLE SCHEMA 0
-- ORDS ENABLE OBJECT 0
--
-- ERRORS 0
-- WARNINGS 1
view raw emp_journal_ddl.sql hosted with ❤ by GitHub


データベース・アクションの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は以下になります。

-- create tables
create table emp (
empno number,
ename varchar2(50),
job varchar2(50),
mgr number,
hiredate date,
sal number,
comm number,
deptno number
)
;
-- history tracking
create sequence history_seq;
create table history (
id number primary key,
table_name varchar2(128),
column_name varchar2(128),
action varchar2(1) check (action in ('I','U','D')),
action_date date,
action_by varchar2(255),
data_type varchar2(255),
pk1 number,
tab_row_version integer,
old_vc varchar2(4000),
new_vc varchar2(4000),
old_number number,
new_number number,
old_date date,
new_date date,
old_ts timestamp,
new_ts timestamp,
old_tswtz timestamp with time zone,
new_tswtz timestamp with time zone,
old_tswltz timestamp with local time zone,
new_tswltz timestamp with local time zone,
old_clob clob,
new_clob clob,
old_blob blob,
new_blob blob
)
/
create index history_idx1 on history (pk1);
create index history_idx2 on history (table_name, column_name);
create or replace view history_v as
select id,
table_name,
column_name,
decode(action,'U','Update','D','Delete') action,
action_date,
action_by,
pk1 table_primary_key,
tab_row_version table_row_version,
decode(data_type,
'NUMBER',old_number||' > '||new_number,
'VARCHAR2',substr(old_vc,1,50)||' > '||substr(new_vc,1,50),
'DATE',to_char(old_date,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_date,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP',to_char(old_ts,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_ts,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP WITH TIMEZONE',to_char(old_tswtz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswtz,'YYYYMMDD HH24:MI:SS'),
'TIMESTAMP WITH LOCAL TIMEZONE',to_char(old_tswltz,'YYYYMMDD HH24:MI:SS')||' > '||to_char(new_tswltz,'YYYYMMDD HH24:MI:SS'),
'BLOB','length '||sys.dbms_lob.getlength(old_blob)||' > '||' length '||sys.dbms_lob.getlength(new_blob),
'CLOB',sys.dbms_lob.substr(old_vc,50,1)||' > '||sys.dbms_lob.substr(new_vc,50,1)
) change
from history
/
create or replace trigger emp_aud
after update or delete on emp
for each row
declare
t varchar2(128) := 'EMP';
u varchar2(128) := nvl(sys_context('APEX$SESSION','APP_USER'),user);
begin
if updating then
if (:old.empno is null and :new.empno is not null) or
(:old.empno is not null and :new.empno is null) or
:old.empno != :new.empno then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'EMPNO', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.empno, :new.empno);
end if;
if (:old.ename is null and :new.ename is not null) or
(:old.ename is not null and :new.ename is null) or
:old.ename != :new.ename then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'ENAME', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.ename, :new.ename);
end if;
if (:old.job is null and :new.job is not null) or
(:old.job is not null and :new.job is null) or
:old.job != :new.job then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'JOB', :old.id, null, 'U', sysdate, u, 'VARCHAR2', :old.job, :new.job);
end if;
if (:old.mgr is null and :new.mgr is not null) or
(:old.mgr is not null and :new.mgr is null) or
:old.mgr != :new.mgr then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'MGR', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.mgr, :new.mgr);
end if;
if (:old.hiredate is null and :new.hiredate is not null) or
(:old.hiredate is not null and :new.hiredate is null) or
:old.hiredate != :new.hiredate then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'HIREDATE', :old.id, null, 'U', sysdate, u, 'DATE', :old.hiredate, :new.hiredate);
end if;
if (:old.sal is null and :new.sal is not null) or
(:old.sal is not null and :new.sal is null) or
:old.sal != :new.sal then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'SAL', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.sal, :new.sal);
end if;
if (:old.comm is null and :new.comm is not null) or
(:old.comm is not null and :new.comm is null) or
:old.comm != :new.comm then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'COMM', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.comm, :new.comm);
end if;
if (:old.deptno is null and :new.deptno is not null) or
(:old.deptno is not null and :new.deptno is null) or
:old.deptno != :new.deptno then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'DEPTNO', :old.id, null, 'U', sysdate, u, 'NUMBER', :old.deptno, :new.deptno);
end if;
elsif deleting then
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'EMPNO', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.empno, :new.empno);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'ENAME', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.ename, :new.ename);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_vc, new_vc
) values (
history_seq.nextval, t, 'JOB', :old.id, null, 'D', sysdate, u, 'VARCHAR2', :old.job, :new.job);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'MGR', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.mgr, :new.mgr);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_date, new_date
) values (
history_seq.nextval, t, 'HIREDATE', :old.id, null, 'D', sysdate, u, 'DATE', :old.hiredate, :new.hiredate);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'SAL', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.sal, :new.sal);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'COMM', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.comm, :new.comm);
insert into history (
id, table_name, column_name, pk1, tab_row_version, action, action_date, action_by, data_type, old_number, new_number
) values (
history_seq.nextval, t, 'DEPTNO', :old.id, null, 'D', sysdate, u, 'NUMBER', :old.deptno, :new.deptno);
end if;
end emp_aud;
/
view raw quicksql-history.sql hosted with ❤ by GitHub


変更履歴を保存する表は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 Archive
Flashback 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;
/
以上でFlashback Data Archiveによって、表EMPの変更履歴が保持されるようになりました。Flashback Version Queryを使って、変更履歴を確認してみます。

表EMPの変更履歴を一覧します。検索時に利用可能な行データ疑似列と、保存されているアプリケーション・コンテキストAPEX$SESSIONのAPP_IDおよびAPP_USERを出力する列に含めます。DBMS_FLASHBACK_ARCHIVE.GET_SYS_CONTEXTを使用しています。

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のアプリケーション開発の参考になれば幸いです。

完
投稿者 時刻:
ラベル: , ,