A few developers approached me and asked for the easiest way to add historization to a certain table containing master data. As someone familiar with system versioned tables in the context of SQL 2011, SQL-Server (as standard feature) and PostgreSQL (as proposal), I was decently sure that Oracle would support that standard feature.
However, after reading and checking the whole day, I cannot find any references to actual SYSTEM VERSIONED tables. For reference, here's what the Wikipedia says about the standard and Oracles implementation:
...
Definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME annotation and WITH SYSTEM VERSIONING modifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rows
...
Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[10] Notably both of Oracle's implementations depend on the database transaction log and so only allow temporal queries against recent changes which are still being retained for backup.
....
...
Definition of system-versioned tables (elsewhere called transaction time tables), using the PERIOD FOR SYSTEM_TIME annotation and WITH SYSTEM VERSIONING modifier. System time periods are maintained automatically. Constraints for system-versioned tables are not required to be temporal and are only enforced on current rows
...
Oracle Oracle 12c supports temporal functionality in compliance with SQL:2011.[9] Versions 10g and 11g implement the time-sliced queries in what they call Flashback Queries, using the alternative syntax AS OF TIMESTAMP.[10] Notably both of Oracle's implementations depend on the database transaction log and so only allow temporal queries against recent changes which are still being retained for backup.
However, even following the referenced link I cannot find a single reference that shows how to get Oracle to version the rows for me. All examples reference only manual updates for the validity from and to columns.
I think I could implement something like SCD2 from Data Warehousing, but that's just a development pattern with no reference how the validity is actually updated. I would really like an easy solution for the developers, as doing the updates for the validity manually is error prone (overlapping is a factor) and more work than I think is necessary.
Is there really no system versioning in Oracle for what the SQL 2011 standard describes?
