-- 3. View all versions of the row over time (Audit Trail) SELECT txn_id, amount, status, VERSIONS_STARTTIME AS change_time, VERSIONS_OPERATION AS operation_type FROM transactions VERSIONS BETWEEN TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' DAY) AND SYSTIMESTAMP WHERE txn_id = 1;
-- Update the data (this creates a historical version) UPDATE transactions SET amount = 600.00 WHERE txn_id = 1; COMMIT; oracle db enterprise edition
We define the retention policy (5 years). VERSIONS_STARTTIME AS change_time
First, we create a tablespace to hold the historical data. This keeps the audit data separate from the live data. oracle db enterprise edition
Automatically track every change made to a transactions table for 5 years for compliance purposes, allowing queries to see the data "as of" any point in time.