Transactions are recorded in the log/archive log files.
From there, you can find such things as
- what values were changed
- time of change within +/- 3 seconds
- who changed it under what context (values in
The "time of transaction" comes from the SCN. As such, the time resolution is "+/- 3 seconds".
If you need a higher resolution, then the Business needs to accept some schema modifications.
alter table T add (
time_of_insert timestamp default systimestamp -- possibly INVISIBLE
VERSIONS BETWEEN Queries
A VERSIONS BETWEEN query adds the following pseudo columns
- VERSIONS_OPERATION - (I)nsert, (U)pdate, (D)elete
"how far back you can go" is dependent on the UNDO tablespace. Which is, usually, 1 hour. YMMV.
To extended the range, use Flashback Data Archive.
Prior to 11g, the only way to access "when a transaction occurs" is to use LogMiner.
This can be done through the DBMS_LOGMNR Package.
Flashback Data Archive
11g introduced a feature called Flashback Data Archive.
- Extends the range of AS OF and VERSIONS BETWEEN queries.
- runs in the background.
- Reads the log/archive log files (I believe it uses LogMiner also)
- records the values in a separate table
- "Old values" are automatically drop
- "Old Values" is determined by your Flashback Archive destination.
This feature is included with all editions of Oracle starting with 188.8.131.52. Prior to 184.108.40.206, you have to have a specific EE add-on ($$$$).
(*) With 12c, Flashback Data Archive can optionally record the values of SYS_CONTEXT also.
Here are some additional URLs regarding Flashback Data Archive