Rich Hickeys - 'Database as a Value' concept & Oracle 12c 'Flashback Archive'

For a long time, I used triggers when creating Oracle applications with input protocols.
I often had the same performance problem as Barbara in the following article:
http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
-----------
The better way leads use to use flashback archive as a table property.

The most important new features are:
* Own logging can be omitted because every change is available as a chronological log.
* A chronological protocol helps to identify unnecessary program steps (for example, delete and re-create rather than merge) and thus optimize.
* For debugging, the state of the database can be retrieved before a program crashes.
* Synchronization jobs that regularly process last updates only need to remember the last call time
to find the last updates on the next call.
* Accounting rules can be adhered to.
- The assignment of data entry to a responsible person and time.
- Entries are not overwritten but automatically canceled and entered again
and that is not the quality of any program. It can not be bypassed.
-----------
I'm glad that these flashback methods in Oracle 12c are now finally available and that they could easily be used. If the FLASHBACK ARCHIVE and ROW ARCHIVAL properties are set for all normal tables in a schema (for recording time) and dbms_flashback_archive.set_context_level has been set (for recording context inc. responsible persons), then an Immutable Database is created with completely new, really wonderful features. When points in time is available as an additional dimension to all objects, then objects become facts and the "Epochal Time Model" can be used.

Quick learning:
(Rich Hickey: Database as a Value)
https://youtu.be/EKdV1IgAaFc?t=12m13s
Minute 12 to 16

Famous applications that make use of this model are the Mac OS Time Machine from Apple and Source Tree for git.
----
and for Oracle see:

https://youtu.be/ZU9OnoHhbNM?t=26m15s
--- Flashback Data Archive ---
* Now available in all licensed editions
* Replaces the trigger approach to populate history tables
* Designate a tablespace to hold history data along with the retention period for the data
+ Specify FLASHBACK ARCHIVE for tables ore ALTER TABLE
* Flashback data can be used
* to look at historical data at a point in time
* to correct data committed to a base table
* Supported with most of the common DDL commands against the source table

--- In-Database Archiving ---
* Enhancement to Flashback Data Archive
* Mark table rows as invisible (logical delete)
* ALTER TABLE ... ROW ARCHIVAL

----
Relevant information from Connor McDonalds article in excerpts:

discover Oracle Flashback’s Oracle Flashback Data Archive feature:
“Flashback Data Archive provides the ability to track and store all transactional
changes to a table over its lifetime. It is no longer necessary to build
this intelligence into your application. A Flashback Data Archive is useful for
compliance with record stage policies and audit reports.”


... a better way.:
First she allocates some storage to hold her archive data:

SQL> create tablespace SPACE_FOR_ARCHIVE
  2  datafile 'C:\ORACLE\DB12\ARCH_SPACE.DBF'
  3  size 50G;

Tablespace created.

Next, she creates a new structure, called a flashback archive, which defines the retention duration of the row change history for any table placed into it.

SQL> CREATE FLASHBACK ARCHIVE longterm
  2  TABLESPACE space_for_archive
  3  RETENTION 1 YEAR;

Flashback archive created

And then she simply associates her HR tables with the flashback archive.

SQL> ALTER TABLE EMPLOYEES FLASHBACK ARCHIVE LONGTERM;

Table altered.

Code Listing 8: Execution plan for tables associated with Flashback Data Archive

SQL> set autotrace traceonly explain
SQL> select * from EMPLOYEES AS OF TIMESTAMP SYSDATE-3;

Code Listing 9: Flashback Version Query

SQL> select EMPLOYEE_ID, FIRST_NAME, JOB_ID, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         nvl(VERSIONS_OPERATION,'I') OP
  4  from EMPLOYEES
  5  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  6  where EMPLOYEE_ID = 100
  7  order by EMPLOYEE_ID, ts;

Enable the capture of context-level information automatically and without changes to the base tables.
Barbara contacts her administrator and requests context-level tracking to be enabled for Flashback Data Archive:

SQL> exec dbms_flashback_archive.set_context_level('ALL');

Code Listing 10: Flashback Version Query with context information

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
  2         VERSIONS_STARTTIME TS,
  3         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER') AS who,
  4         dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program
  5  from EMPLOYEES
  6  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  7  where EMPLOYEE_ID = 100
  8  order by EMPLOYEE_ID, ts;

-------------------

Oracle Flashback Version Query - retrieve metadata and historical data
for a specific time interval. You can view all the rows of a table that
ever existed during a given time interval. Metadata about the different
versions of rows includes start and end time, type of change operation,
and identity of the transaction that created the row version. You use
the VERSIONS BETWEEN clause of the SELECT statement to create a
Flashback Version Query.

https://docs.oracle.com/cd/B14117_01/appdev.101/b10795/adfns_fl.htm

------------------
I learned from Tom Kyte that in 12c many insert triggers (also for Apex 5 programs) are obsolete.
https://youtu.be/-mK75UyIblQ?t=1m
Minute 1.00 bis 2:14

Code Listing 11: A new template for common rows initialization
create sequence department_seq;

create table departments (
    department_id                  -- number generated by default on null as identity -- oder explizit
                                   number default on null department_seq.nextval
                                   constraint departments_id_pk primary key,
    name                           varchar2(255) not null,
    location                       varchar2(4000),
    country                        varchar2(4000),
    created                        date default on null sysdate not null,
    created_by                     varchar2(255) default on null nvl(sys_context('APEX$SESSION','APP_USER'),user) not null,
    updated                        date default on null sysdate not null,
    updated_by                     varchar2(255) default on null nvl(sys_context('APEX$SESSION','APP_USER'),user) not null
)
;

-- triggers
create or replace trigger tgr_departments_bu
    before update
    on departments
    for each row
begin
    :new.updated := sysdate;
    :new.updated_by := nvl(sys_context('APEX$SESSION','APP_USER'),user);
end departments_biu;
/

------------------
Catch the Apex Session Context:

I have learned that with the option
exec dbms_flashback_archive.set_context_level ('ALL');
the entire context including custom context is recorded.
The following query returns in the Apex environment (z.B. Apex 5/ SQL Workshop / SQL Commands)
3 Session Variables that are probably recorded. (Namespace APEX$SESSION, Attribute: APP_SESSION, APP_USER, WORKSPACE_ID)


SELECT * FROM SESSION_CONTEXT;

-- Then there are also a bunch of variables in the namespace USERENV:
select
          sys_context ('userenv','ACTION') ACTION,
          sys_context ('userenv','AUDITED_CURSORID') AUDITED_CURSORID,
          sys_context ('userenv','AUTHENTICATED_IDENTITY') AUTHENTICATED_IDENTITY,
          sys_context ('userenv','AUTHENTICATION_DATA') AUTHENTICATION_DATA,
          sys_context ('userenv','AUTHENTICATION_METHOD') AUTHENTICATION_METHOD,
          sys_context ('userenv','BG_JOB_ID') BG_JOB_ID,
          sys_context ('userenv','CLIENT_IDENTIFIER') CLIENT_IDENTIFIER,
          sys_context ('userenv','CLIENT_INFO') CLIENT_INFO,
          sys_context ('userenv','CURRENT_BIND') CURRENT_BIND,
          sys_context ('userenv','CURRENT_EDITION_ID') CURRENT_EDITION_ID,
          sys_context ('userenv','CURRENT_EDITION_NAME') CURRENT_EDITION_NAME,
          sys_context ('userenv','CURRENT_SCHEMA') CURRENT_SCHEMA,
          sys_context ('userenv','CURRENT_SCHEMAID') CURRENT_SCHEMAID,
          sys_context ('userenv','CURRENT_SQL') CURRENT_SQL,
          sys_context ('userenv','CURRENT_SQLn') CURRENT_SQLn,
          sys_context ('userenv','CURRENT_SQL_LENGTH') CURRENT_SQL_LENGTH,
          sys_context ('userenv','CURRENT_USER') CURRENT_USER,
          sys_context ('userenv','CURRENT_USERID') CURRENT_USERID,
          sys_context ('userenv','DATABASE_ROLE') DATABASE_ROLE,
          sys_context ('userenv','DB_DOMAIN') DB_DOMAIN,
          sys_context ('userenv','DB_NAME') DB_NAME,
          sys_context ('userenv','DB_UNIQUE_NAME') DB_UNIQUE_NAME,
          sys_context ('userenv','DBLINK_INFO') DBLINK_INFO,
          sys_context ('userenv','ENTRYID') ENTRYID,
          sys_context ('userenv','ENTERPRISE_IDENTITY') ENTERPRISE_IDENTITY,
          sys_context ('userenv','FG_JOB_ID') FG_JOB_ID,
          sys_context ('userenv','GLOBAL_CONTEXT_MEMORY') GLOBAL_CONTEXT_MEMORY,
          sys_context ('userenv','GLOBAL_UID') GLOBAL_UID,
          sys_context ('userenv','HOST') HOST,
          sys_context ('userenv','IDENTIFICATION_TYPE') IDENTIFICATION_TYPE,
          sys_context ('userenv','INSTANCE') INSTANCE,
          sys_context ('userenv','INSTANCE_NAME') INSTANCE_NAME,
          sys_context ('userenv','IP_ADDRESS') IP_ADDRESS,
          sys_context ('userenv','ISDBA') ISDBA,
          sys_context ('userenv','LANG') LANG,
          sys_context ('userenv','LANGUAGE') LANGUAGE,
          sys_context ('userenv','MODULE') MODULE,
          sys_context ('userenv','NETWORK_PROTOCOL') NETWORK_PROTOCOL,
          sys_context ('userenv','NLS_CALENDAR') NLS_CALENDAR,
          sys_context ('userenv','NLS_CURRENCY') NLS_CURRENCY,
          sys_context ('userenv','NLS_DATE_FORMAT') NLS_DATE_FORMAT,
          sys_context ('userenv','NLS_DATE_LANGUAGE') NLS_DATE_LANGUAGE,
          sys_context ('userenv','NLS_SORT') NLS_SORT,
          sys_context ('userenv','NLS_TERRITORY') NLS_TERRITORY,
          sys_context ('userenv','OS_USER') OS_USER,
          sys_context ('userenv','POLICY_INVOKER') POLICY_INVOKER,
          sys_context ('userenv','PROXY_ENTERPRISE_IDENTITY') PROXY_ENTERPRISE_IDENTITY,
          sys_context ('userenv','PROXY_USER') PROXY_USER,
          sys_context ('userenv','PROXY_USERID') PROXY_USERID,
          sys_context ('userenv','SERVER_HOST') SERVER_HOST,
          sys_context ('userenv','SERVICE_NAME') SERVICE_NAME,
          sys_context ('userenv','SESSION_EDITION_ID') SESSION_EDITION_ID,
          sys_context ('userenv','SESSION_EDITION_NAME') SESSION_EDITION_NAME,
          sys_context ('userenv','SESSION_USER') SESSION_USER,
          sys_context ('userenv','SESSION_USERID') SESSION_USERID,
          sys_context ('userenv','SESSIONID') SESSIONID,
          sys_context ('userenv','SID') SID,
          sys_context ('userenv','STATEMENTID') STATEMENTID,
          sys_context ('userenv','TERMINAL') TERMINAL
        from dual;

-- If I combine this correctly, the history query (Code Listing 10) would need to be expandable as follows:

SQL> select EMPLOYEE_ID, FIRST_NAME, VACATION_BALANCE,
        VERSIONS_STARTTIME TS,
       nvl(dbms_flashback_archive.get_sys_context(versions_xid,('APEX$SESSION','APP_USER'),
        dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER')) AS who,
        dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE') AS program,
        dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION​','APP_SESSION') AS app_session
        dbms_flashback_archive.get_sys_context(versions_xid, 'APEX$SESSION​','WORKSPACE_ID​​') AS workspace_id
  from EMPLOYEES
  versions between timestamp  timestamp '2016-01-11 08:20:00' and systimestamp
  where EMPLOYEE_ID = 100
  order by EMPLOYEE_ID, ts;

----
Here are some Screenshots from my Schema Riser Application that displays a Change Log Report.

Screenshot 1

Screenshot 2

----
Regards
Dirk Strack