Oracle 12c has added a feature called valid time temporal which determines when a table row is valid.  This is done by defining the table with two hidden columns giving a start and stop top for data validity.   This article describes the setup of valid time temporal and its use in Oracle database.

 

1. Logon to your oracle server as oracle software owner and logon to sqlplus as sysdba.

 

[root@linux2 ~]# su – oracle

Last login: Wed Mar 14 06:50:11 EST 2015 on pts/1

Enter database to use:

1 – ORCL

2 – CDB1

Option >

1

[oracle@linux2 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 15 07:50:07 2015

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

 

SQL>

 

2. Valid-time periods can be set during table creation or after with ‘ALTER TABLE’. In this example we will create a table test.test_time with the following statement:   NOTE: using the ‘PERIOD FOR’ clause to implicitly defined columns for valid time temporal.

 

CREATE TABLE test.test_times (pers_id NUMBER PRIMARY KEY,

 first_name VARCHAR2( 20),

last_name VARCHAR2( 25),

PERIOD FOR probation);

 

3. When the table is described only the following columns are displayed.

 

DESCRIBE probationary_emps

Name                    Null?                     Type

————–         ———                                 ————–

EMP_ID                NOT NULL           NUMBER

FIRST_NAME                                     VARCHAR2( 20)

LAST_NAME                                       VARCHAR2( 25)

 

4. Quering the USER_TAB_COLS shows additional hidden columns

 

column column_name format a15

column data_type format a30

column hidden format a8

set pagesize 500

SELECT column_name,

data_type,

 hidden_column AS HIDDEN FROM dba_tab_cols

WHERE table_name = ‘TEST_TIMES’;

 

 

SQL> column column_name format a15

column data_type format a30

column hidden format a8

set pagesize 500

SELECT column_name,

data_type,

 hidden_column AS HIDDEN FROM dba_tab_cols

WHERE table_name = ‘TEST_TIMES’;

SQL> SQL> SQL> SQL>   2    3    4

COLUMN_NAME     DATA_TYPE                      HIDDEN

————— —————————— ——–

LAST_NAME       VARCHAR2                       NO

FIRST_NAME      VARCHAR2                       NO

PERS_ID         NUMBER                         NO

PROBATION       NUMBER                         YES

PROBATION_END   TIMESTAMP(6) WITH TIME ZONE    YES

PROBATION_START TIMESTAMP(6) WITH TIME ZONE    YES

 

6 rows selected.

 

SQL>

 

 

5. insert the following records into new table:

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(1, ‘John’, ‘Doe’, to_date(’01-JAN-16′,’DD-MON-YY’),to_date(’01-APR-16′,’DD-MON-YY’));

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(2, ‘Jan’, ‘Doe’, to_date(’01-May-16′,’DD-MON-YY’),to_date(’01-SEP-16′,’DD-MON-YY’));

insert into test.test_times(pers_id, first_name, last_name, probation_start, probation_end)

values(3, ‘Bill’, ‘Jones’, to_date(’01-OCT-16′,’DD-MON-YY’),to_date(’01-JAN-17′,’DD-MON-YY’));

 

commit;

 

6. Now select all records in the table test_times.

 

SQL> select * from test.test_times;

 

   PERS_ID FIRST_NAME           LAST_NAME

———- ——————– ————————-

         1 John                 Doe

         2 Jan                  Doe

         3 Bill                 Jones

 

SQL>

 

7. Now perform the same select but limiting results by hidden column of PROBATION_END

 

SQL> select * from test.test_times where probation_end<to_date(’01-NOV-16′,’DD-MON-YY’);

 

   PERS_ID FIRST_NAME           LAST_NAME

———- ——————– ————————-

         1 John                 Doe

         2 Jan                  Doe

 

SQL>

 

8. This completes the setup and testing of Valid Time Temporal in Oracle 12c.

 

Larry Catt

OCP