Oracle Temporal Validity
Version 20c

GENERAL
 
CREATE TABLE
In-Database Archiving ALTER TABLE <[schema_name.]table_name> ADD PERIOD FOR <valid_time_column_name> [(start_time_column, end_time_column)];
conn uwclass/uwclass@pdbdev

CREATE TABLE emp_indbarch (
employee_id    NUMBER(6),
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)) ROW ARCHIVAL;

ALTER TABLE emp_indbarch
ADD CONSTRAINT pk_emp_indbarch
PRIMARY KEY (employee_id);

desc emp_indbarch

col column_name format a17
col data_type format a10

SELECT column_name, data_type, column_id, segment_column_id,
internal_column_id, hidden_column, char_length
FROM user_tab_cols
WHERE table_name = 'EMP_INDBARCH'
ORDER BY 4;

INSERT INTO emp_indbarch VALUES
(251, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('15-MAR-2020'),
 'IT_PROG', 50000, .5, 103, 60);

INSERT INTO emp_indbarch VALUES
(252, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('15-MAR-2020'),
 'IT_PROG', 40000, .4, 103, 60);

INSERT INTO emp_indbarch VALUES
(253, 'Scott', 'Tiger', 'scott.tiger@oracle.com', '206-555-1212', TO_DATE('15-MAR-2020'),
 'IT_PROG', 30000, .35, 103, 60);

COMMIT;

col ora_archive_state format a18

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- -----------------
        251 0
        252 0
        253 0

-- Insert a value into ORA_ARCHIVE_STATE to set inactive
UPDATE emp_indbarch
SET ora_archive_state = '20'
WHERE employee_id = 252;

1 row updated.

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- -----------------
        251 0
        252 20
        253 0

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;

Session altered.

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- -----------------
        251 0
        252 20
        253 0

ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;

Session altered.

SELECT employee_id, ora_archive_state
FROM emp_indbarch;

EMPLOYEE_ID ORA_ARCHIVE_STATE
----------- -----------------
        251 0
        253 0
 
ALTER TABLE
Add In-Database Archiving to an Existing Table CREATE TABLE [schema_name.]table_name(
<column_specification);

ALTER TABLE <table_name> PERIOD FOR <valid_time_column> [(<start_time_column>, <end_time_column>];
CREATE TABLE addPerDef(
rid  NUMBER,
dob1 DATE,
dob2 DATE);

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ADDPERDEF'
ORDER BY column_id;

COLUMN_NAME       HID
----------------- ---
RID               NO
DOB1              NO
DOB2              NO

ALTER TABLE AddPerDef ADD PERIOD FOR track_time(dob1, dob2);

Table altered.

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ADDPERDEF'
ORDER BY column_id;

COLUMN_NAME       HID
----------------- ---
RID               NO
DOB1              NO
DOB2              NO
TRACK_TIME        YES
 
DROP
Remove Temporal Validity columns from a table ALTER TABLE <[schema_name.]table_name> DROP PERIOD FOR <valid_time_column_name>;
SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ADDPERDEF'
ORDER BY column_id;

COLUMN_NAME       HID
----------------- ---
RID               NO
DOB1              NO
DOB2              NO
TRACK_TIME        YES

ALTER TABLE addPerDef DROP (PERIOD FOR tracktime);

Table altered.

SELECT column_name, hidden_column
FROM user_tab_cols
WHERE table_name = 'ADDPERDEF'
ORDER BY column_id;

COLUMN_NAME       HID
----------------- ---
RID               NO
DOB1              NO
DOB2              NO
 
FLASHBACK QUERY
Temporal AS OF Query SELECT <values>
FROM [<schema_name.>]<table_name>
AS OF PERIOD FOR <period_name> (<period_value>)
[WHERE <where_clause>]
[GROUP BY <group_by_clause>]
[HAVING <having_clause>]
[ORDER BY <order_by_clause>];
CREATE TABLE tv_airplanes AS
SELECT * FROM airplanes;

Table created.

ALTER TABLE tv_airplanes
ADD PERIOD FOR transaction_range(order_date, delivered_date);

Table altered.

SELECT COUNT(*)
FROM tv_airplanes;

  COUNT(*)
----------
    250000

SELECT COUNT(*)
FROM tv_airplanes
AS OF PERIOD FOR transaction_range SYSDATE;

  COUNT(*)
----------
       110


SELECT COUNT(*)
FROM tv_airplanes
AS OF PERIOD FOR transaction_range TO_DATE('31-DEC-2034');

  COUNT(*)
----------
     13725
 
FLASHBACK VERSIONS
Temporal Flashback Version Query SELECT <values>
FROM [<schema_name.>]<table_name>
VERSIONS PERIOD FOR <period_name> BETWEEN <period_value> AND <period_value>
[WHERE <where_clause>]
[GROUP BY <group_by_clause>]
[HAVING <having_clause>]
[ORDER BY <order_by_clause>];
CREATE TABLE tv_airplanes AS
SELECT * FROM airplanes;

Table created.

ALTER TABLE tv_airplanes
ADD PERIOD FOR transaction_range(order_date, delivered_date);

Table altered.

SELECT COUNT(*)
FROM tv_airplanes;

  COUNT(*)
----------
    250000

SELECT COUNT(*)
FROM tv_airplanes
VERSIONS PERIOD FOR transaction_range BETWEEN SYSDATE AND SYSDATE+1000;

  COUNT(*)
----------
      5110


SELECT COUNT(*)
FROM tv_airplanes
VERSIONS PERIOD FOR transaction_range BETWEEN SYSDATE+1000 AND TO_DATE('31-DEC-2034');

  COUNT(*)
----------
     24645
 
Temporal Validity Demo
Follow the link below to DBMS_FLASHBACK_ARCHIVE to view additonal Temporal Validity Demos

Related Topics
DBMS_FLASHBACK_ARCHIVE
FLASHBACK
FLASHBACK ARCHIVE
FLASHBACK QUERY
FLASHBACK VERSION
TABLES
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx