4.4 Implementing Temporal Validity

«« Previous
Next »»

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

2. Creating a Table with a Valid-Time Dimension


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

sqlplus system/oracle@localhost:1521/noncdb

2. 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;

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

alter table hr.emp_temp add period for valid_time;

4. View the HR.emp_temp table structure.

desc hr.emp_temp

Implementing Temporal Validity

Notice that the valid-time columns are not displayed because they are hidden columns and must be explicitly included in a query.

5. 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;

Implementing Temporal Validity

6. 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;

7. 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
order by 2;

Implementing Temporal Validity

3. Querying a Table with Valid Time Support


Here are some examples of queries on tables with valid time support.

1. Find all employee records which are still valid as of 01-JUN-2011.

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
as of period for valid_time to_date('01-JUN-2011')
order by 2;

Implementing Temporal Validity

2. Find all employee records which are valid between 01-SEP-1995 and 01-SEP-1996. This query should return all records where valid_time_start >= 01-SEP-1995 and valid_time_end <= 01-SEP-1996.

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 
versions period for valid_time 
between to_date('01-SEP-1995') and to_date('01-SEP-1996') 
order by 

Implementing Temporal Validity


4. Setting Visibility of Temporal Data with DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME


To set the visibility of data in tables with temporal support, you use the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time procedure. 

1. Only make visible the currently valid records in HR.emp_temp.
exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

2. Query HR.emp_temp. Which records are currently valid and will be displayed?

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
order by 2; 

Implementing Temporal Validity

3. Make all records in HR.emp_temp visible, regardless of their temporal validity status.

exec dbms_flashback_archive.enable_at_valid_time('ALL');

4. Query HR.emp_temp. All records should be displayed..

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
order by 2; 

Implementing Temporal Validity

5. Resetting Your Environment

Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

1. Drop the HR.emp_temp table.
drop table hr.emp_temp purge;

«« Previous
Next »»