ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling
complimentary technical Workshops on Database Security for the first 30
Oracle Database customers located anywhere in North America, EMEA, LATAM, or
APAC that send an email to
asra_us@oracle.com. Request a Workshop for
your organization today.
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
