About
Temporal validity permits to retrieve the data as it was in a point in time by defining a validity period for which a given record is considered valid.
This time-based validity is determined by the start and end dates of a period (defined by two columns named also dimension). (This columns can be hidden to the user).
By declaring a valid point in time in a session, it gives time validity access transparently to all tables that holds temporal support. The user don't need explicitly to add a where clause statement such as for instance
where start_date >= mydate and end_date <= mydate
Articles Related
Example
The best example in a datawarehouse environment is a Slowly Changing Dimensions type 2 where we trace all changes of a record.
For instance, when the employee Nico changes this name to “Gerard Nico” the 30 Juny 2011 and that we want to trace this change we end up with the following data structure.
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Nico 01-JAN-10 30-JUN-11
100 Gerard Nico 30-JUN-11
Where the start time and the end time column define what Oracle calls the temporal validity of the data.
Privileges
The database user must have:
- the SELECT privilege on the objects in the select statement
- not necessarily others elevated privileges in order to use the Temporal validity functionality (create table, select)
- the privilege to use the dbms_flashback_archive PL/SQL package in order to set the point in time validity of a session.
grant all on "SYS"."dbms_flashback_archive" to user;
Period Syntax definition
The validity period column is defined in a create table statement or in an alter table statement.
It is defined with the following columns:
- a period column. It's an INVISIBLE virtual column with the period name of data type NUMBER.
- and two columns that define the start and end data time of the period. This columns can be:
- of a data time data type (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE)
- modified and inserted
Alter Example:
ALTER TABLE my_emp ADD PERIOD FOR period_name (start_time_column, end_time_column);
where:
- the (start_time_column, end_time) statement is optional. If they are not defined in the statement , they are automatically created by Oracle Database as INVISIBLE (you cannot make them VISIBLE) with the following name:
- PERIOD_NAME_START
- and PERIOD_NAME_END
Row validity
If you will find NULL in | The Row is valid |
---|---|
start_time_column | before the value of end_time_column |
end_time_column | after the value of start_time_column. |
neither column | between the values of start_time_column and end_time_column, inclusive. |
both columns | all time values |
Limitations
Temporal validity
- Temporal validity is not supported with a multitenant container database (CDB). The database must not be a Pdb. Oracle will return no errors but will also not return the expected result.
- ILM is not supported with OLTP table compression for Temporal Validity. Segment-level ILM and compression is supported if partitioned on the end-time columns.
- ILM is not supported with OLTP table compression for in-database archiving. Segment-level ILM and compression is supported if partitioned on the ORA_ARCHIVE_STATE column.
Flashback Queries
Steps
The following step by step was extracted and extended from the Oracle® Database Development Guide: Design Basic: 1.9.4 Temporal Validity
Management of the validity period
The PERIOD FOR statement enable Temporal Validity for a table.
- Create table: The create table statement with Periode definition
CREATE TABLE my_emp(
empno NUMBER,
last_name VARCHAR2(30),
start_time TIMESTAMP,
end_time TIMESTAMP,
PERIOD FOR user_valid_time (start_time, end_time));
- Alter table Add: If the table exists already Add Periode clause
ALTER TABLE my_emp ADD PERIOD FOR user_valid_time (start_time, end_time);
- Alter table Drop: Drop Periode clause
ALTER TABLE my_emp DROP (PERIOD FOR user_valid_time);
Temporal Validity Columns
If the periode clause doesn't set two columns explicitly two hidden columns are added to the table:
- PERIOD_NAME_START
- and PERIOD_NAME_END
This columns are not visible in the output of:
- a SELECT statement (unless the columns are explicitly stated)
Name Null? Type
----------------------- -------- ----------------
EMPNO NUMBER
LAST_NAME VARCHAR2(30)
START_TIME TIMESTAMP(6)
END_TIME TIMESTAMP(6)
You can see them with the *_TAB_COLS tables (USER_TAB_COLS,DBA_TAB_COLS, ALL_TAB_COLS).
SELECT
SUBSTR( COLUMN_NAME, 1, 22 ) NAME,
SUBSTR( DATA_TYPE, 1, 28 ) DATA_TYPE,
HIDDEN_COLUMN
FROM
USER_TAB_COLS
WHERE
TABLE_NAME = 'MY_EMP';
NAME DATA_TYPE HID
--------------- --------------- ---
END_TIME TIMESTAMP(6) NO
START_TIME TIMESTAMP(6) NO
LAST_NAME VARCHAR2 NO
EMPNO NUMBER NO
USER_VALID_TIME NUMBER YES
Insertion of sample data
select * from NLS_SESSION_PARAMETERS where parameter = 'NLS_TIMESTAMP_FORMAT';
DD-MM-YYYY HH.MI.SSXFF AM
-- Employee 100 with history
INSERT INTO my_emp VALUES (100, 'Nick', null, '01-01-2010');
INSERT INTO my_emp VALUES (100, 'Nico', '01-01-2010', '30-06-2011');
INSERT INTO my_emp VALUES (100, 'Gerard Nico', '30-06-2011', Null);
-- Employee 101 with history
INSERT INTO my_emp VALUES (101, 'Daniel', '01-01-2011', '30-07-2011');
INSERT INTO my_emp VALUES (101, 'Dany', '30-07-2011', null);
-- Employee 102 without
INSERT INTO my_emp VALUES (102, 'Jos', '01-01-2013', null);
-- Employee 103 with Nulls
INSERT INTO my_emp VALUES (103, 'Ghost', null, null);
Retrieve Rows (with visibility definition of temporal data)
The visibility of temporal data can be define:
Session
Set session-level visibility of temporal data usingdbms_flashback_archive.enable_at_valid_time with the following option:
- currently valid data (as of sysdate)
You can also implement session-level Flashback using the DBMS_FLASHBACK package.
Current
Current records is the currently valid data (SYSDATE)
BEGIN
dbms_flashback_archive.enable_at_valid_time('CURRENT');
END;
/
select * from my_emp;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Gerard Nico 30-JUN-11
101 Dany 30-JUL-11
102 Jos 01-JAN-13
103 Ghost
All
All records is the default.
BEGIN
sys.dbms_flashback_archive.enable_at_valid_time('ALL');
END;
/
select * from my_emp;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Nick 01-JAN-10
100 Nico 01-JAN-10 30-JUN-11
100 Gerard Nico 30-JUN-11
101 Daniel 01-JAN-11 30-JUL-11
101 Dany 30-JUL-11
102 Jos 01-JAN-13
103 Ghost
As of
BEGIN
sys.dbms_flashback_archive.enable_at_valid_time('ASOF',TO_DATE( '30-06-2011', 'DD-MM-YYYY' ));
END;
/
select * from my_emp;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Gerard Nico 30-JUN-11
101 Daniel 01-JAN-11 30-JUL-11
103 Ghost
Query
Standard
With a standard query, the default row validity period is ALL. It means that you will see all data.
SELECT
*
FROM
my_emp;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Nick 01-JAN-10
100 Nico 01-JAN-10 30-JUN-11
100 Gerard Nico 30-JUN-11
101 Daniel 01-JAN-11 30-JUL-11
101 Dany 30-JUL-11
102 Jos 01-JAN-13
103 Ghost
Flashback Query
Oracle Flashback Query is used to retrieve rows from table based on whether they are considered valid as of a specified time or during a specified time period. See flashback_query_clause - Oracle® Database SQL Language Reference 12c Release 1 (12.1)
Flashback Query retrieve data from a table, view, or materialized view based on time dimensions associated with the data.
- As of. Select of only one employee in a point in time.
-- What's the valid information as of today?
SELECT * from my_emp AS OF PERIOD FOR user_valid_time SYSDATE;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Gerard Nico 30-JUN-11
101 Dany 30-JUL-11
102 Jos 01-JAN-13
103 Ghost
-- What's the valid information as of 30-06-2011?
SELECT * FROM my_emp
AS OF PERIOD
FOR user_valid_time TO_DATE( '30-06-2011', 'DD-MM-YYYY' )
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Gerard Nico 30-JUN-11
101 Daniel 01-JAN-11 30-JUL-11
103 Ghost
- Versions. Select of multiple version of the employee rows.
SELECT
*
FROM
my_emp VERSIONS PERIOD
FOR user_valid_time BETWEEN TO_DATE( '01-01-2010','DD-MM-YYYY' ) AND SYSDATE
WHERE
empno = 100;
EMPNO LAST_NAME START_TIME END_TIME
----- ------------ ---------- ----------
100 Nico 01-JAN-10 30-JUN-11
100 Gerard Nico 30-JUN-11