Creating a system-period temporal table

You can define a pair of tables that are used to maintain the current data as well as the historical data for a table. These tables are called a system-period temporal table and a history table.

A system-period temporal table is defined just like any other table except that it must have three additional timestamp columns and a system period. The history table is defined with identical columns to the system-period temporal table. An ALTER TABLE statement is used to connect the two tables in a versioned relationship.

For example, suppose you want to keep track of all changes to the DEPARTMENT table. You would define the table as follows:

CREATE OR REPLACE TABLE DEPARTMENT
      (DEPTNO    CHAR(3)       NOT NULL,
       DEPTNAME  VARCHAR(36)   NOT NULL,
       MGRNO     CHAR(6),
       ADMRDEPT  CHAR(3)       NOT NULL, 
       LOCATION  CHAR(16),
       START_TS  TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN,
       END_TS    TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END,
       TS_ID     TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID,
      PERIOD SYSTEM_TIME (START_TS, END_TS),
      PRIMARY KEY (DEPTNO))

You would define the corresponding history table as follows:

CREATE TABLE DEPARTMENT_HIST LIKE DEPARTMENT

Then you would tie them together in a versioning relationship like this:

ALTER TABLE DEPARTMENT ADD VERSIONING USE HISTORY TABLE DEPARTMENT_HIST

Once versioning is defined for the system-period temporal table, updates and deletes to it cause the version of the row prior to the change to be inserted as a row in the history table. The special row begin and row end timestamp columns are set by the system to indicate the time span when the data for the historical row was the active data.

You can write a query that will automatically return data from both the system-period temporal table and the history table.

For example, to see what the DEPARTMENT table looked like six months ago, issue the following query:

SELECT * FROM DEPARTMENT FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 6 MONTHS