Alternative to Triggers I am working on an oracle database and want to record the exact time of any transaction happening on a specific table in the schema. Using triggers on that table is out of question as the client do not want to use it for unknown reasons, also it is not possible to alter the existing tables 

Can anyone tell me if there is any other way to achieve this. I just want to record the time of the transaction and the ROWID of the inserted or updated row in a separate table.
Background Info

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 SYS_CONTEXT) (*)


TIME

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.

example:

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_STARTSCN
VERSIONS_STARTTIME
VERSIONS_ENDSCN
VERSIONS_ENDTIME
VERSIONS_XID
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.

LogMiner

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.

This feature


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 11.2.0.4. Prior to 11.2.0.4, you have to have a specific EE add-on ($$$$).

(*) With 12c, Flashback Data Archive can optionally record the values of SYS_CONTEXT also.

Other Links

Here are some additional URLs regarding Flashback Data Archive


http://www.oracle.com/technetwork/issue-archive/2016/16-mar/o26performance-2925662.html
https://youtu.be/2RXUUrxcKrw?list=PLlo3zrPbMP9_HDjsq9lcdi8RyiIvqfmBy