rea

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;

    View the HR.emp_temp table structure.

    desc hr.emp_temp

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

    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;

    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;

Querying a Table with Valid Time Support

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

    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;

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

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. 

    Only make visible the currently valid records in HR.emp_temp.

    exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

    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;

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

    exec dbms_flashback_archive.enable_at_valid_time('ALL');

    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;

Resetting Your Environment

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

    Drop the HR.emp_temp table.

    drop table hr.emp_temp purge;

Summary

    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

    Resources

    Credits

    • 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.
Print:
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.