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
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.
The database user must have:
grant all on "SYS"."dbms_flashback_archive" to user;
The validity period column is defined in a create table statement or in an alter table statement.
It is defined with the following columns:
Alter Example:
ALTER TABLE my_emp ADD PERIOD FOR period_name (start_time_column, end_time_column);
where:
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 |
The following step by step was extracted and extended from the Oracle® Database Development Guide: Design Basic: 1.9.4 Temporal Validity
The PERIOD FOR statement enable Temporal Validity for a table.
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 my_emp ADD PERIOD FOR user_valid_time (start_time, end_time);
ALTER TABLE my_emp DROP (PERIOD FOR user_valid_time);
If the periode clause doesn't set two columns explicitly two hidden columns are added to the table:
This columns are not visible in the output of:
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
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);
The visibility of temporal data can be define:
Set session-level visibility of temporal data usingdbms_flashback_archive.enable_at_valid_time with the following option:
You can also implement session-level Flashback using the DBMS_FLASHBACK package.
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 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
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
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
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.
-- 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
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