This tutorial covers the steps for adding a valid time
dimension on a table, and various methods for querying the table
and retrieving records based on a specified valid time value or
Time to Complete
Approximately 15 minutes
Valid time temporal support in Oracle Database enables you to
associate a valid time dimension with a table and to have data
be visible depending on its time-based validity, as determined
by the start and end dates or timestamps of the period for which
a given record is considered valid. Examples of time-based
validity can include the hire and termination dates of an
employee in a Human Resources application, the effective date of
coverage for an insurance policy, and the effective date of a
change of address for a customer or client.
Valid time temporal support is typically used with Oracle Flashback technology, to perform AS OF and VERSIONS BETWEEN queries that specify the valid time period. You can also use the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time procedure to specify a option for the visibility of table data: all table data (the default), valid data as of a specified time, or currently valid data within the valid time period at the session level.
Some scenarios where valid time temporal support can be useful include:
- Information Lifecycle Management (ILM) and any other application where it is important to know when certain data became valid (from the application's perspective) and when it became invalid (if it ever did)
- Data correction where incorrect data needs to be retained and marked with the period when it was considered valid, and where the correct data needs to be visible as currently valid
You will be creating a copy of the HR.employees table, and
adding a valid-time dimension which will record each employee's
start date and end date (if they left the company). You will
then issue various queries against the table to experiment with
the various options of retrieving records based on their
temporal validity. This tutorial is done in a non-container
database because temporal validity is not supported for
Before starting this tutorial, you should:
- Have installed Oracle Database 12c.
- Have installed the Sample Schemas in a non-container
The environment used in the development of this tutorial is as follows:
- ORACLE_HOME: /u01/app/oracle/product/12.1.0
- TNS Listener port: 1521
- Container databases:
- SID: cdb1
- SID: cdb2
- Pluggable databases (in cdb1):
- Non-CDB SID: noncdb
Creating a Table with a Valid-Time Dimension
Querying a Table with Valid Time Support
Here are some examples of queries on tables with valid time support.
Setting Visibility of Temporal Data with
To set the visibility of data in tables with temporal support, you use the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time procedure.
Resetting Your Environment
Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.
In this tutorial, you have learned how to:
- Create a table with a valid-time dimension
- Query a table with valid time support
- Set session-level visibility of temporal data using dbms_archive_flashback.enable_at_valid_time
- To learn more about Oracle Database 12c refer to additional OBEs in the Oracle Learning Library
- Contributors: Jean-Francois Verrier, Dominique Jeunot
To help navigate this Oracle by Example, note the following:
- Hiding Header Buttons:
- Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
- Topic List Button:
- A list of all the topics. Click one of the topics to navigate to that section.
- Expand/Collapse All Topics:
- To show/hide all the detail for all the sections. By default, all topics are collapsed
- Show/Hide All Images:
- To show/hide all the screenshots. By default, all images are displayed.
- To print the content. The content currently displayed or hidden will be printed.
To navigate to a particular section in this tutorial, select the topic from the list.