Creating a Table with Temporal Validity

View All Scripts Login to Run Script

Script Name Creating a Table with Temporal Validity
Description Administering temporal validity
Area SQL General / Data Manipulation
Referenced In Database VLDB and Partitioning Guide
Contributor Oracle
Created Monday October 05, 2015

Statement 1
Temporal Validity enables you to track time periods for real world validity. Valid times can be set by users and applications for data, and data can be selected by a specified valid time, or a valid time range. Note that temporal validity is not supported with a multitenant container database (CDB) In this example, we create a table with an employee tracking timestamp using the PERIOD FOR clause. The start and end track timestamps can be used to track valid times for specific operations.
Create the EMPLOYEES_TRACK_TIME table
```
CREATE TABLE employees_track_time ( 
       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), 
       PERIOD FOR emp_track_time)
```
```
Table created.
```

Statement 2

Display structure of the table, including hidden columns (emp_track_time_start, emp_track_time_end).

SELECT SUBSTR(COLUMN_NAME,1,22) NAME, SUBSTR(DATA_TYPE,1,28) DATA_TYPE, COLUMN_ID AS COL_ID, 
     SEGMENT_COLUMN_ID AS SEG_COL_ID, INTERNAL_COLUMN_ID AS INT_COL_ID, HIDDEN_COLUMN  
     FROM USER_TAB_COLS WHERE TABLE_NAME='EMPLOYEES_TRACK_TIME'

 
NAME DATA_TYPE COL_ID SEG_COL_ID INT_COL_ID HIDDEN_COLUMN
EMP_TRACK_TIME_START TIMESTAMP(6) WITH TIME ZONE  - 1 1 YES
EMP_TRACK_TIME_END TIMESTAMP(6) WITH TIME ZONE  - 2 2 YES
EMP_TRACK_TIME NUMBER  -  - 3 YES
EMPLOYEE_ID NUMBER 1 3 4 NO
FIRST_NAME VARCHAR2 2 4 5 NO
LAST_NAME VARCHAR2 3 5 6 NO
EMAIL VARCHAR2 4 6 7 NO
PHONE_NUMBER VARCHAR2 5 7 8 NO
HIRE_DATE DATE 6 8 9 NO
JOB_ID VARCHAR2 7 9 10 NO
SALARY NUMBER 8 10 11 NO
COMMISSION_PCT NUMBER 9 11 12 NO
MANAGER_ID NUMBER 10 12 13 NO
DEPARTMENT_ID NUMBER 11 13 14 NO

14 rows selected.

NAME	DATA_TYPE	COL_ID	SEG_COL_ID	INT_COL_ID	HIDDEN_COLUMN
EMP_TRACK_TIME_START	TIMESTAMP(6) WITH TIME ZONE	-	1	1	YES
EMP_TRACK_TIME_END	TIMESTAMP(6) WITH TIME ZONE	-	2	2	YES
EMP_TRACK_TIME	NUMBER	-	-	3	YES
EMPLOYEE_ID	NUMBER	1	3	4	NO
FIRST_NAME	VARCHAR2	2	4	5	NO
LAST_NAME	VARCHAR2	3	5	6	NO
EMAIL	VARCHAR2	4	6	7	NO
PHONE_NUMBER	VARCHAR2	5	7	8	NO
HIRE_DATE	DATE	6	8	9	NO
JOB_ID	VARCHAR2	7	9	10	NO
SALARY	NUMBER	8	10	11	NO
COMMISSION_PCT	NUMBER	9	11	12	NO
MANAGER_ID	NUMBER	10	12	13	NO
DEPARTMENT_ID	NUMBER	11	13	14	NO

Statement 3

Insert a row into the table.

INSERT INTO employees_track_time (emp_track_time_start, emp_track_time_end, employee_id, first_name, 
   last_name, email, hire_date, job_id, salary, manager_id, department_id)  
   VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris',  
           TIMESTAMP '2012-11-30 12:00:01 Europe/Paris', 251, 'Scott', 'Tiger', 
          'scott.tiger@example.com', DATE '2009-05-21', 'IT_PROG', 60000, 103, 60)

1 row(s) inserted.

Statement 4

Insert a row into the table.

INSERT INTO employees_track_time (emp_track_time_start, emp_track_time_end, employee_id, first_name, 
     last_name, email, hire_date, job_id, salary, manager_id, department_id)   
     VALUES (TIMESTAMP '2009-06-01 12:00:01 Europe/Paris',  
             TIMESTAMP '2012-12-31 12:00:01 Europe/Paris', 252, 'Jane', 'Lion', 
             'jane.lion@example.com', DATE '2009-06-11', 'IT_PROG', 60000, 103, 60)

1 row(s) inserted.

Statement 5

Insert a row into the table.

INSERT INTO employees_track_time (emp_track_time_start, emp_track_time_end, employee_id, first_name, 
     last_name, email, hire_date, job_id, salary, manager_id, department_id)   
     VALUES (TIMESTAMP '2011-07-01 12:00:01 Europe/Paris',  
             TIMESTAMP '2014-08-31 12:00:01 Europe/Paris', 253, 'Marie', 'Smith', 
             'marie.smith@example.com', DATE '2011-06-10', 'IT_PROG', 60000, 103, 60)

1 row(s) inserted.

Statement 6

Update manager_id for a specified tracking start time.

UPDATE employees_track_time set manager_id = 105 
       WHERE emp_track_time_start <= TIMESTAMP '2009-06-01 12:00:01 Europe/Paris'

2 row(s) updated.

Statement 7

Display all the rows in the table.

SELECT employee_id, last_name, first_name, manager_id FROM employees_track_time

 
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER_ID
251 Tiger Scott 105
252 Lion Jane 105
253 Smith Marie 103

3 rows selected.

Statement 8
Delete rows with specified values for time start and end columns. Note that no rows are deleted for the DELETE statement because none are in the specified track time.
```
DELETE employees_track_time WHERE emp_track_time_end < TIMESTAMP '2001-12-31 12:00:01 Europe/Paris'
```
```
0 row(s) deleted.
```

EMPLOYEE_ID	LAST_NAME	FIRST_NAME	MANAGER_ID
251	Tiger	Scott	105
252	Lion	Jane	105
253	Smith	Marie	103

Statement 9

Display rows that are in a specified time tracking period using start and end times.

SELECT employee_id FROM employees_track_time  
       WHERE emp_track_time_start > TIMESTAMP '2009-05-31 12:00:01 Europe/Paris' AND  
             emp_track_time_end < TIMESTAMP '2012-12-01 12:00:01 Europe/Paris'

 
EMPLOYEE_ID
251

Statement 10
Display rows that are within a specified time tracking period. When you are finished testing the example, you can clean up the environment by dropping the table (DROP TABLE employees_track_time).
```
SELECT employee_id FROM employees_track_time AS OF PERIOD FOR  
       emp_track_time TIMESTAMP '2012-12-01 12:00:01 Europe/Paris'
```
```
 
EMPLOYEE_ID
252
253

2 rows selected.
```

EMPLOYEE_ID
251

EMPLOYEE_ID
252
253

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database