Oracle 12C new featureTemporal Virtual Columns start date and end date for Dimension tables

Oracle 12C new featureTemporal  Virtual Columns start date and end date for Dimension tables

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

Example:

------------

Creating one dim table 

CREATE TABLE hr.emp_dim AS
SELECT employee_id, first_name, salary FROM hr.employees;

Adding Valid_time into new dim table:

ALTER TABLE emp_dim ADD period FOR valid_time;

if you see desc emp_dim
we can see only three columns employee_id,first_name,salary
we can't see  valid_time_start and valid_time_end hidden columns.

If we want to see the hidden columns we need to include in SQL select query.

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


We can insert start date and end date values using below queries.

UPDATE emp_dim
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 emp_dim
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 emp_dim
SET valid_time_start = to_date('20-MAY-1998','dd-MON-yyyy')
WHERE first_name    IN ('Neena','Nancy','Valli');


COMMIT;