Oracle Database 26ai will be available on generic Linux platforms in January and soon on AIX and Windows
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;
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