Alternative to Triggers

Question

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.

Answer 1

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

Answer 2

Oracle auditing comes with Oracle Enterprise installations. There is a multitude of options that are described here and here. For your question:

AUDIT SELECT,UPDATE, INSERT, DELETE
     ON <SchemaName>.<TableName>
     BY ACCESS
     WHENEVER SUCCESSFUL;

There are some initialization parameters that must be set before this will work: Log on as a user with sysdba and issue this:

ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;

Then shutdown and restart the system.

The actions that have been audited are in SYS.AUD$ and can be viewed with any user with SYSDBA or DBA privileges. There are a number of Oracle provided views which provide more detail:

SELECT view_name
FROM   dba_views
WHERE  view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;

From here

Answer 3

Using triggers on that table is out of question as the client do not want to use it ... also it is not possible to alter the existing tables

So they just want "Magic" to happen without changing anything.
That tells me that

• they don't understand what they're working with - OK, that's possibly understandable
• they don't trust you, as the Database Administrator, to advise them - that's more worrying.

Push back on this so-called "Requirement"; challenge them.

Triggers are the way to do this. They work, they're reliable, they don't leave any sneaky "gaps" that people can slip through and make unrecorded changes and they don't introduce the potentially huge overhead or complexity of system-wide options like Oracle's own Auditing.

Answer 4

It is possible that Materialized View Logs might be what they're after. However, I couldn't say what the impact of using them would be.

In my job, we use them as part of a comparison process for testing purposes. We have a database with the old code and one with the new code, and we run the standard daily runs against both. Materialized view logs capture the changes, and we compare these to work out if there were differences or not.

That may or may not be something that would work for you, but beware, it isn't what materialized view logs were designed to do (they're there to be used by materialized views). You can also add your own columns to be populated, e.g. time of the DML, on top of the columns you get out-of-the-box (you'd have to use the default keyword to set the values on insert of those additional columns).

Answer 5

If you turn on rowdependencies at the table level (increasing the storage required for each row by 6 bytes), the ORA_ROWSCN pseudocolumn will give you the SCN when that row was last changed. With norowdependencies(the default), it will give you the SCN when the block the row is in was last changed.

The SCN can be turned into a timestamp using the scn_to_timestamp() function.

See the documentation for more details.

This is no trigger though; you have to explicitly query each row for this information.