Temporal Database Functionality

Oracle Database 12c provides support for Transaction Time (also known as System Time) and Valid Time (also known as Business Time or Application Time), in compliance with the SQL:2011 standard. Transaction Time is tracked with Flashback Data Archive, which was part of the now defunct Total Recall database option. Temporal database functionality is available in Oracle Database 12c (and with Flashback Data Archive only in Oracle Database, in both Enterprise Edition and Standard Edition. The temporal capabilities built in to Oracle Database 12c provide SQL extensions for manipulating data based on both Transaction and Valid Time dimensions, allowing application developers to develop and maintain bitemporal applications with much less effort and much less code.

Temporal functionality in Oracle Database 12c simplifies application development when the application needs to track historical changes to data over time - Transaction Time - and when the application needs to track real world validity of objects or concepts that are represented by rows in database tables. For example, an insurance policy application will need to track the start date and end date for each insurance policy, and the history of changes to each policy. Similarly, a stock price application will need to track the start time and end time for each stock price, and a history of corrections to those prices.

In addition to helping application developers be more productive, Flashback Data Archive is also useful in regulatory environments, as the history tables are designed to be tamper-proof - so they can provide auditors with a clear and immutable history of changes made to critical data.

While almost all of the temporal functionality in Oracle Database 12c is available in both Standard Edition and Enterprise Edition, there is one exception: optimization of Flashback Data Archive history tables is included in the Advanced Compression Option. History table optimization applies Advanced Row Compression, Advanced LOB Compression, and Advanced LOB Deduplication to data in the history tables, resulting in 2x-4x compression ratios, and significantly improving the performance of Flashback Query statements that access historical data.


Technical Information

Related Technologies

Oracle Open World 2014 Banner

In-Memory Replay Banner