Oracle Temporal Validity
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. 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.
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  
 
CREATE TABLE
In-Database Archiving ALTER TABLE <[schema_name.]table_name> ADD PERIOD FOR <valid_time_column_name>;
conn uwclass/uwclass@pdbdev

CREATE TABLE employees_indbarch (
employee_id    number(6) not null,
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;

desc employees_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 = 'EMPLOYEES_INDBARCH'
ORDER BY 4;

INSERT INTO employees_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 employees_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 employees_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 employees_indbarch;

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

SELECT employee_id, ora_archive_state
FROM employees_indbarch;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

SELECT employee_id, ora_archive_state
FROM employees_indbarch;

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

SELECT employee_id, ora_archive_state
FROM employees_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

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-2014 Daniel A. Morgan All Rights Reserved