Written by: ilmarkerm

Category: Blog entry

Published: May 30, 2022

In Oracle Autonomous database one major application feature is missing – Flashback Data Archive. Purpose of that feature was archiving all data changes seamlessly to the application, no old-school trigger-based data archival/auditing needed. One major disadvantage of triggers is that end user session needs to wait for the trigger to execute, they become part of the end user transaction. FDA lets the users just declare for how long all changes need to be retained (even years) and it collected the data to be archived in the background, from UNDO data. It also had the possibility to add context from the user session that made the data change. Although FDA has been quite troublesome/buggy component, I’ve always liked its functionality.

FDA is part of all Oracle Database editions – except Oracle Autonomous Database. Reference

I currently do not see any other possibility than to go back to old-trigger based solutions, but how to make it as easy to maintain as possible? And keep the modification context.

Traditional way using triggers is to store :OLD (and :NEW ?) values in a separate table, while having a separate audit table for each source table. But these kinds of triggers need to be recreated (and history tables maintained also) whenever the source table structure changes. Sadly there is no way to serialize :OLD/:NEW or access them in any dynamic way.

There are ready made packages for implementing this style of auditing, for example this one by Connor McDonald.

I decided to create my own to make the maintenance much simpler. Store everything as JSON, and store only the new row version.

• It is very easy to generate JSON object from a table row
• I would argue, that there is no need to record :OLD row in the history, if :NEW is always recorded. And since every history record has a context attached, it makes more logical sense to me that you store the session context together with the :NEW row version – that this person changed the row to this new version. Not that this person removed that :OLD row version.
• Changes to source table do not require any changes to triggers nor the history table.
• Yes, JSON wastes more diskspace.
• It is very easy to query JSON as relational data in Oracle.
• Currently the code expects each table has a primary key named ID. I’m not going to change it to make it more generic, you are welcome to change the code for yourself 🙂 As always, don’t take the code blindly from the internet, understand it before you use it.