Implementing Temporal Validity

Overview

Purpose

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 range.

Time to Complete

Approximately 15 minutes

Introduction

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

Scenario

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 pluggable databases.

Prerequisites

Before starting this tutorial, you should:

Have installed Oracle Database 12c.
Have installed the Sample Schemas in a non-container database.

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):

pdb1
pdb2

Non-CDB SID: noncdb

Creating a Table with a Valid-Time Dimension

Using SQL*Plus, connect to noncdb database as sys.

sqlplus system/oracle@localhost:1521/noncdb

Create a copy of the HR.employees table. Call the new table HR.emp_temp.

create table hr.emp_temp as
select employee_id, first_name, salary
from hr.employees
where rownum <=10;

Add a valid-time dimension to the HR.emp_temp table.

alter table hr.emp_temp add period for valid_time;

images/t10101.jpg — View the HR.emp_temp table structure.

desc hr.emp_temp

images/t10102.jpg — Display all records in HR.emp_temp, including the valid-time columns.

select first_name,
to_char(valid_time_start,'dd-mon-yyyy') "Start",
to_char(valid_time_end,'dd-mon-yyyy') "End"
from hr.emp_temp;

Update the records and populate the valid-time columns.

update hr.emp_temp
set valid_time_start = to_date('01-JUN-1995','dd-MON-yyyy'), valid_time_end = to_date('15-SEP-2010','dd-MON-yyyy')
where first_name in ('Lex','Alexander','Bruce','David','Daniel');

update hr.emp_temp
set valid_time_start = to_date('01-AUG-1999','dd-MON-yyyy'), valid_time_end = to_date('01-MAR-2012','dd-MON-yyyy')
where first_name in ('Steven','Diana');

update hr.emp_temp
set valid_time_start = to_date('20-MAY-1998','dd-MON-yyyy')
where first_name in ('Neena','Nancy','Valli');

commit;