Posted by Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer.
Create a tablespace to hold your history, and create a flashback archive using that space allocation.
SQL> create tablespace SPACE_FOR_ARCHIVE
2 datafile 'C:\ORACLE\DB11\ARCH_SPACE.DBF'
3 size 100M;
Tablespace created.
SQL> CREATE FLASHBACK ARCHIVE longterm
2 TABLESPACE space_for_archive
3 RETENTION 1 YEAR;
Flashback archive created.
Then add your tables to the archive. Some tables will be created automatically to support being able to record all of the changes to that table for as long as the archive duration specifies
SQL> ALTER TABLE EMP FLASHBACK ARCHIVE LONGTERM;
Table altered.
SQL> select table_name
2 from user_tables
3 /
TABLE_NAME
---------------------------
SYS_FBA_HIST_71036
SYS_FBA_TCRV_71036
SYS_FBA_DDL_COLMAP_71036
EMP
And then voila! We can track changes to our table over time, well beyond the typical undo_retention setting for the database.
SQL> select empno, ename, job, sal, comm,
2 nvl(VERSIONS_STARTTIME,LAST_MOD) TS
3 ,nvl(VERSIONS_OPERATION,'I') op
4 from EMP
5 versions between timestamp
6 timestamp '2014-02-11 20:12:00' and
7 systimestamp
8 order by empno;
EMPNO ENAME JOB SAL COMM TS O
---------- ---------- --------- ---------- ---------- ------------ -
7369 SMITH CLERK 806 08.10.51 PM I
7369 SMITH SALES 8060 1000 08.12.10 PM U
7499 ALLEN SALESMAN 1606 300000000 08.10.51 PM I
7521 WARD SALESMAN 1256 500000000 08.10.51 PM I
7566 JONES MANAGER 2981 08.10.51 PM I
...
7900 JAMES CLERK 956 08.10.51 PM I
7902 FORD ANALYST 3006 08.10.51 PM I
7934 MILLER CLERK 1306 08.10.51 PM I
7934 MILLER CLERK 1306 08.12.10 PM D
But sometimes we want to know more about the history. We want to know who did the change, what machine were they on, etc etc. Auditors tend to be a little snippy about those kind of things 
- You: “I can confirm that the SALARY field was updated at 9:01am on January 25th”
- Auditor: “And who did that change?”
- You: “Er….um…..someone”
But it might not be practical to augment all of your tables with WHO, WHEN, WHERE etc columns to capture that information. That might involve a lot of application changes and testing.
If you’re on 12c, you might be in luck. Flashback Data Archive can capture additional information, assuming your application is taking advantage of the myriad of instrumentation facilities offered by the database.
We simply turn on context tracking, which can capture information on a per transaction basis
SQL> begin
2 dbms_flashback_archive.set_context_level(
3 level=> 'ALL');
4 end;
PL/SQL procedure successfully completed.
Now when we perform changes to the table, we can look at the transaction ID (XID) for that change.
SQL> update EMP
2 set sal = sal*10
3 where empno = 7499;
1 row updated.
SQL> commit;
SQL> select XID from SYS_FBA_HIST_510592;
XID
----------------
09000B00C7080000
And using that XID, we can pick up the context information about that transaction
SQL> set serverout on
SQL> begin
2 dbms_output.put_line(
3 dbms_flashback_archive.get_sys_context (
4 '09000B00C7080000', 'USERENV', 'SESSION_USER'));
5
6 dbms_output.put_line(
7 dbms_flashback_archive.get_sys_context (
8 '09000B00C7080000', 'USERENV', 'HOST'));
9
10 dbms_output.put_line(
11 dbms_flashback_archive.get_sys_context (
12 '09000B00C7080000', 'USERENV', 'MODULE'));
13 end;
14 /
SCOTT
WORKGROUP\XPS
SQL*Plus
How much more ? Well, quite a lot actually !
SQL> desc SYS.SYS_FBA_CONTEXT_AUD
Name Null? Type
------------------------- -------- ------------------
XID RAW(8)
ACTION VARCHAR2(256)
AUTHENTICATED_IDENTITY VARCHAR2(256)
CLIENT_IDENTIFIER VARCHAR2(256)
CLIENT_INFO VARCHAR2(256)
CURRENT_EDITION_NAME VARCHAR2(256)
CURRENT_SCHEMA VARCHAR2(256)
CURRENT_USER VARCHAR2(256)
DATABASE_ROLE VARCHAR2(256)
DB_NAME VARCHAR2(256)
GLOBAL_UID VARCHAR2(256)
HOST VARCHAR2(256)
IDENTIFICATION_TYPE VARCHAR2(256)
INSTANCE_NAME VARCHAR2(256)
IP_ADDRESS VARCHAR2(256)
MODULE VARCHAR2(256)
OS_USER VARCHAR2(256)
SERVER_HOST VARCHAR2(256)
SERVICE_NAME VARCHAR2(256)
SESSION_EDITION_NAME VARCHAR2(256)
SESSION_USER VARCHAR2(256)
SESSION_USERID VARCHAR2(256)
SESSIONID VARCHAR2(256)
TERMINAL VARCHAR2(256)
SPARE VARCHAR2(256)
And since we can use our flashback query syntax to pick up all of the transaction ID’s
SQL> select empno, ename, job, sal, comm,
2 VERSIONS_XID
3 from EMP
4 versions between timestamp
5 timestamp '2014-02-11 20:12:00' and
6 systimestamp
7 order by empno;
EMPNO ENAME JOB SAL COMM VERSIONS_XID
---------- ---------- --------- ---------- ---------- ----------------
7369 SMITH CLERK 806
7369 SMITH SALES 8060 1000 09001C00E04A0000
7499 ALLEN CLERK 16060 300000000 0A000A0024080000
7499 ALLEN SALESMAN 16060 300000000 09000B00C7080000
7499 ALLEN SALESMAN 1606 300000000
7521 WARD SALESMAN 1256 500000000
we can also take advantage of that new dictionary view SYS.SYS_FBA_CONTEXT_AUD to get all the context information for each transaction.
SQL> select ...
2 from
3 ( select
4 empno, ename, job,
5 sal, comm, VERSIONS_XID
6 from EMP
7 versions between timestamp
8 timestamp '2014-02-11 20:12:00' and
9 systimestamp
10 ) e, SYS.SYS_FBA_CONTEXT_AUD a
11 where e.VERSIONS_XID = a.XID;
Flashback Data Archive is pretty nifty in 12c.
https://docs.oracle.com/database/121/ADFNS/adfns_flashback.htm#ADFNS01011
Learning is not a spectator sport 
The strange things about SYS.SYS_FBA_CONTEXT_AUD are follows:
1. it does not contain any indexes
2. it resides in SYSTEM tablespace
3. MOS does not show any hits for it
It means that if either your transaction volume or the Flashback Archive retention is big enough you will end up with a huge table in your SYSTEM tablespace.
There are several obvious reasons why it is bad (including not so obvious ones like: http://blog.tanelpoder.com/2008/09/02/oracle-hidden-costs-revealed-part2-using-dtrace-to-find-why-writes-in-system-tablespace-are-slower-than-in-others/ ).
So, currently, out of the box I do not see that the functionality in question has been designed for high volumes.
I have been waiting for a while till Oracle provides fine-grained Flashback Archive functionality with more control about what should be archived like:
1. I do not want to my application be logged. It generates a huge amount of changes
2. I do want to store in a Flashback Archive any changes generated by non-application users (like the application administrators and support staff). These are small part of changes generated by my application.
We can accomplish fine-grained auditing using, obviously, FGA, Unified Database Auditing (really neat features), triggers. But we do not have such level of data logging as if we use Flashback Data Archive (particularly, we do not have the image of data before DML) and we do not have a way to easily select our data as we do with Flashback Archive.
And, of course, we can process all of the redo generated by the production database using GoldenGate/Streams and do whatever we want with it (that’s what I do now to satisfy the complex security requirements of my organization).
Thanks for your comment. MOS note 1073588.1 talks about moving objects out of SYSTEM, and whilst SYS_FBA_CONTEXT_AUD is not specifically mentioned, I would *hypothesize* that this is an oversight and will be picked up in due course.
Whilst I can’t speak for Support, I imagine you could make a pretty good case for it move to (say) SYSAUX without it being an impact on a running system.
SQL> select empno, ename, job, sal, comm,
2 nvl(VERSIONS_STARTTIME,LAST_MOD) TS
3 ,nvl(VERSIONS_OPERATION,’I’) op
4 from EMP
5 versions between timestamp
6 timestamp ‘2014-02-11 20:12:00’ and
7 systimestamp
8 order by empno;
Is there a way to bring data from the day that I enabled my FDA on a table?
If you look at the above query, I need to remember FDA enabled date and put that time stamp in the SELECT query.
It should be something like below…
from EMP
versions between timestamp
timestamp and
systimestamp
order by empno;
Have you tried VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
When I use “VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE”, I only get the changes done today. If I replace MINVALUE with an actual timestamp, I get all the results correctly. Any idea why this is happening. The problem is that I have to specify a not-too-old date or I will get ora-01555.
Will try.
It worked 😉
I have another issue in the form of accessing FDA table. Could you check this out?
—–
SQL> alter session set current_schema=rcm_app;
SQL> select a.rule_id from XXRCM_CORE_PROCESSING_RULE a where a.rule_id = 1414903 ;
RULE_ID
———-
1414903
SQL>
SQL> select rule_id FROM XXRCM_CORE_PROCESSING_RULE VERSIONS BETWEEN SCN MINVALUE and MAXVALUE
2 WHERE rule_id = 1414903 ;
select rule_id FROM XXRCM_CORE_PROCESSING_RULE VERSIONS BETWEEN SCN MINVALUE and MAXVALUE
WHERE rule_id = 1414903
ORA-01031: insufficient privileges
SQL>
——-
I am able to select data from the regular table, no issues.
But when I use FDA table, I get “ORA-01031: insufficient privileges” error.
What kind of grants did I suppose to have to query FDA table?
You need the FLASHBACK privilege
I also realized that storing sys_context all creates a lot of problems. Beside the ones you mentioned I found that the flashback query gets very slow after a short time.
Do you heard about any Progess in 18c or 19c conserning the performance and space issue in using flashback including sys_context ?
No changes that I’m aware of. I’ve seen some clients utilising the facility to capture the context information, but then intermittently copying it out to their own physical tables at a later time in order to get more control over the queries and the performance.
We ran into similar concerns with sys_fba_context_aud, considered copying to another table but that could not be done ensuring atomicity.
SQL> lock table sys_fba_context_aud in exclusive mode; exec dbms_flashback_archive.purge_context;
BEGIN dbms_flashback_archive.purge_context; END;
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
ORA-06512: at “SYS.DBMS_FLASHBACK_ARCHIVE”, line 57
ORA-06512: at line 1
SQL> truncate table sys_fba_context_aud;
truncate table sys_fba_context_aud
*
ERROR at line 1:
ORA-55622: DML, ALTER and CREATE UNIQUE INDEX operations are not allowed on
table “SYS”.”SYS_FBA_CONTEXT_AUD”
So we tried Unified Audit Trail instead(With the UAT segments moved to a separate tablespace, daily partitioning in place for the UAT readers) which has most of the information sys_fba_context_aud has excluding a few.
create audit policy FBA
actions
delete on xyz.test_table,
insert on xyz.test_table,
update on xyz.test_table;
audit policy FBA;
select
a.*,
b.dbusername
from
(
select
id,
versions_starttime,
versions_operation,
versions_xid
from
xyz.test_table versions between timestamp systimestamp-(10/24) and systimestamp
order by
versions_startscn
) a
left join unified_audit_trail b on a.versions_xid=b.transaction_id
order by
versions_starttime;
Will have to be tested thoroughly though if the volumes are high.
Hi Connor, In Oracle 12c, using FDA, I am trying to build a history table with all operations like Inserts, Updates and Deletes on a table. I am able to capture I, U and D operations on a simple table ( No IOTs). But when the table is truncated, the record is not appearing as Operation = ‘D’ when I use VERSIONS BETWEEN syntax. Do you suggest how to perceive the records as ‘Delete’ when truncate operation happens on the table.
You’re out of luck. Truncate is the most “brutal” of all database operations (even more so than DROP, because you can flashback a drop)
Hi Connor,
When enabling flashback archive to a particular table it doesn’t create the above three tables.
(Oracle 19C)
Do you have any idea?
Thanks & Best Regards,
Wimali
Patience 🙂
We don’t create them immediately…only when there has been enough activity that we can no longer dig around in the undo segments to get your data.
Hi, we are using APEX and enabled the context tracking, when we fetch data from the archive it shows the IP from host, is there workaround to see clients actual IP?
Tks
I don’t think so (natively). Have you tried copying the IP address into one of the standard captured fields (module, action, etc)? (althought I suspect APEX may override that)
Is there a way to identify what column has changed value? I see that there is an update on the row but don’t know how to get what has been changed.
tks
Not without comparing each column value