Oracle Temporal Validity
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version to 12.2.0.1 and version 18.0 is going to be available soon. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c and beyond.

Have you viewed the Library's main Table of Contents? If not ... [Click Here] ... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
Purpose Explain
See: Oracle Database Development Guide for details
also: Temporal Validity Flashback Queries
Queries that combine Temporal Validity and Transaction Time Temporal (tracked using Flashback Data Archive) are called bi-temporal queries.
Dependencies
ALL_TABLES CDB_TAB_COLS USER_TABLES
ALL_TAB_COLS DBA_TABLES USER_TAB_COLS
CDB_TABLES DBA_TAB_COLS  
System Privileges
ALTER TABLE CREATE TABLE  
Period Definition -- this clause is new in 12c and supports Temporal Validity. Further demos can be seen on the Temporal Validity page linked at page bottom.

CREATE TABLE [schema_name.]table_name(
<column_specification);

ALTER TABLE <table_name> PERIOD FOR <valid_time_column> [(<start_time_column>, <end_time_column>];
CREATE TABLE perDef(
rid  NUMBER,
dob1 DATE,
dob2 DATE);

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'PERDEF'
ORDER BY column_id;

ALTER TABLE perDef ADD PERIOD FOR track_time(dob1, dob2);

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'PERDEF'
ORDER BY column_id;
 
CREATE TABLE
In-Database Archiving ALTER TABLE <[schema_name.]table_name> ADD PERIOD FOR <valid_time_column_name> [(start_time_column, end_time_column)];
conn uwclass/uwclass@pdbdev

CREATE TABLE emp_indbarch (
employee_id    NUMBER(6),
first_name     VARCHAR2(20),
last_name      VARCHAR2(25) NOT NULL,
email          VARCHAR2(25) NOT NULL,
phone_NUMBER   VARCHAR2(20),
hire_date      DATE         NOT NULL,
job_id         VARCHAR2(10) NOT NULL,
salary         NUMBER(8,2),
commission_pct NUMBER(2,2),
manager_id     NUMBER(6),
department_id  NUMBER(4)) ROW ARCHIVAL;

ALTER TABLE emp_indbarch
ADD CONSTRAINT pk_emp_indbarch
PRIMARY KEY (employee_id);

desc emp_indbarch

col column_name format a17
col data_type format a10

SELECT column_name, data_type, column_id, segment_column_id,
internal_column_id, hidden_column, char_length
FROM user_tab_cols
WHERE table_name = 'EMP_INDBARCH'
ORDER BY 4;

INSERT INTO emp_indbarch VALUES
(251, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('21-MAY-2013'),
 'IT_PROG', 50000, .5, 103, 60);

INSERT INTO emp_indbarch VALUES
(251, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('21-MAY-2013'),
 'IT_PROG', 40000, .4, 103, 60);

INSERT INTO emp_indbarch VALUES
(252, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('21-MAY-2013'),
 'IT_PROG', 30000, .35, 103, 60);

COMMIT;

col ora_archive_state format a18

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

/* Insert a value into ORA_ARCHIVE_STATE to set inactive */
UPDATE emp_indbarch
SET ora_archive_state = '20'
WHERE employee_id = 252;

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

========================================================

ALTER TABLE timedep ADD PERIOD FOR temporal;

desc timedep

SELECT column_id, column_name, data_type, hidden_column
FROM user_tab_cols
WHERE table_name = 'TIMEDEP';

SQL> alter session set row archival visibility = ACTIVE;

Session altered.

SELECT * FROM timedep;
 
ADD PERIOD
? ?
?
 
FLASHBACK QUERY
Valid query but something needs to be enabled AS OF PERIOD FOR
exec dbms_flashback_archive.enable_at_valid_time('ASOF');

SELECT *
FROM timedep
AS OF PERIOD FOR TO_TIMESTAMP('02-DEC-13 00.00.00.000000 PM -08:00');
? VERSIONS PERIOD FOR
?
 
DROP
Removes temporal validity columns from a table ALTER TABLE <[schema_name.]table_name> DROP PERIOD FOR <valid_time_column_name>;
SELECT column_id, column_name, data_type, hidden_column
FROM user_tab_cols
WHERE table_name = 'TIMEDEP';

ALTER TABLE timedep DROP (PERIOD FOR temporal);

SELECT column_id, column_name, data_type, hidden_column
FROM user_tab_cols
WHERE table_name = 'TIMEDEP';
 
Temporal Validity Demo
Load Demo Data INSERT INTO timedep
SELECT * FROM servers;

COMMIT;

SELECT COUNT(*) FROM timedep;

UPDATE timedep
SET temporal_start = SYSDATE-1
WHERE status = 'Y';

UPDATE timedep
SET temporal_end = SYSDATE
WHERE status <> 'Y';

COMMIT;

exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

col temporal_start format a40
col temporal_end format a40

SELECT srvr_id, temporal, temporal_start, temporal_end
FROM timedep;

SELECT COUNT(*)
FROM timedep
WHERE temporal_end IS NOT NULL;

SELECT COUNT(*)
FROM timedep
WHERE temporal_end IS NULL;

exec dbms_flashback_archive.enable_at_valid_time('ALL');

SELECT COUNT(*)
FROM timedep
VERSIONS PERIOD FOR temporal BETWEEN MINVALUE AND MAXVALUE;

Related Topics
FLASHBACK
FLASHBACK ARCHIVE
FLASHBACK QUERY
TABLES
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved