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.
You would define the
corresponding history table as follows:
CREATE OR REPLACE TABLE DEPARTMENT
(DEPTNO CHAR(3) NOT NULL,
DEPTNAME VARCHAR(36) NOT NULL,
ADMRDEPT CHAR(3) NOT NULL,
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))
Then you would tie them together in a versioning relationship
CREATE TABLE DEPARTMENT_HIST LIKE DEPARTMENT
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.
SELECT * FROM DEPARTMENT FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP - 6 MONTHS