Oracle 12c - Temporal Validity

 

Temporal validity feature allows you to add a valid time dimension to the table data by specifiying the start and end datetimes of the period for which the data should be valid and visible.
 
Flashback query clauses ASOF, VERSIONS can be used to query the data valid for a specified period of time. You can also control the data visibility at the session level by using the DBMS_FLASHBACK_ARCHIVE.enable_at_valid_time procedure.
 
 
 

In this example, Temporal validity feature is used to set the time validity for various account reward offers provided to the customers.Temporal Validity is enabled by using the clause, "add period for" for the table. OFFER_VALID column prefix is used here in this example. Three new hidden columns, viz. OFFER_VALID, OFFER_VALID_START and OFFER_VALID_END  are added to the table.

SQL> select * from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE
--------------- ----------------- ---------- ----------------
	   1001 	       10 CURR			89898
	   1002 	       10 CURR			89898
	   2001 	       20 CURR			89898
	   2002 	       20 CURR			89898
	   1001 	       10 CURR			84372
	   1002 	       10 CURR			84372
	   2001 	       20 CURR			84372
	   2002 	       20 CURR			84372

8 rows selected.

SQL>  alter table acct_offers add period for OFFER_VALID;

Table altered.

SQL>  select table_name,column_name,data_type from user_tab_cols
  2  where table_name='ACCT_OFFERS';

TABLE_NAME		       COLUMN_NAME		      DATA_TYPE
------------------------------ ------------------------------ ------------------------------
ACCT_OFFERS		       OFFER_VALID		      NUMBER
ACCT_OFFERS		       OFFER_VALID_END		      TIMESTAMP(6) WITH TIME ZONE
ACCT_OFFERS		       OFFER_VALID_START	      TIMESTAMP(6) WITH TIME ZONE
ACCT_OFFERS		       ACCT_BRANCH_CODE 	      NUMBER
ACCT_OFFERS		       ACCT_TYPE		      VARCHAR2
ACCT_OFFERS		       REWARDS_VENDOR_ID	      NUMBER
ACCT_OFFERS		       ACCT_REWARDS_ID		      NUMBER

7 rows selected.


SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,OFFER_VALID_START,OFFER_VALID_END
     from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE  OFFER_VALID_STAR OFFER_VALID_END
--------------- ----------------- ---------- ----------------  --------------- ---------------
	   1001 	       10 CURR			89898  
	   1002 	       10 CURR			89898  
	   2001 	       20 CURR			89898  
	   2002 	       20 CURR			89898  
	   1001 	       10 CURR			84372  
	   1002 	       10 CURR			84372 
	   2001 	       20 CURR			84372 
	   2002 	       20 CURR			84372 

8 rows selected.


Time validity is set for various offers by updating the OFFER_VALID_START and OFFER_VALID_END column values.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-MAR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-APR-2014','DD-MON-YYYY') 
  2  where acct_branch_code=89898 and rewards_vendor_id=10;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-APR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-MAY-2014','DD-MON-YYYY') 
  2  where acct_branch_code=89898 and rewards_vendor_id=20;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-APR-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-MAY-2014','DD-MON-YYYY') 
  2  where acct_branch_code=84372 and rewards_vendor_id=20;

2 rows updated.

SQL> update acct_offers set OFFER_VALID_start=to_date('02-MAY-2014','DD-MON-YYYY'), OFFER_VALID_end=to_date('02-JUN-2014','DD-MON-YYYY') 
  2  where acct_branch_code=84372 and rewards_vendor_id=10;

2 rows updated.

SQL> commit;

Commit complete.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   1001 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   1002 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   2001 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   1001 	       10 CURR			84372 02-MAY-2014	   02-JUN-2014
	   1002 	       10 CURR			84372 02-MAY-2014	   02-JUN-2014
	   2001 	       20 CURR			84372 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			84372 02-APR-2014	   02-MAY-2014

8 rows selected.

 

 

Flashback AS OF, VERSIONS queries can be used to find the data valid for a given period of time.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers as of period for OFFER_VALID to_date('10-MAR-2014');

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   1001 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   1002 	       10 CURR			89898 02-MAR-2014	   02-APR-2014



SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers versions period for OFFER_VALID between sysdate-90 and sysdate;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   1001 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   1002 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   2001 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2001 	       20 CURR			84372 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			84372 02-APR-2014	   02-MAY-2014

6 rows selected.

 

DBMS_FLASHBACK_ARCHIVE.ENABLE_VALID_TIME can be set at the session level to define the valid period of time for all the subsequent data operations.

Enable the session to view the offers valid only for the current time
SQL> exec dbms_flashback_archive.enable_at_valid_time('CURRENT');

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   2001 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2001 	       20 CURR			84372 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			84372 02-APR-2014	   02-MAY-2014
 
Enable the session to view and update the offers valid in future, as of 1st JUN 2014.
SQL> exec dbms_flashback_archive.enable_at_valid_time('ASOF',to_date('01-JUN-2014','DD-MON-YYYY'));

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2  to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3  from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   1001 	       10 CURR			84372 02-MAY-2014	   02-JUN-2014
	   1002 	       10 CURR			84372 02-MAY-2014	   02-JUN-2014


SQL> update acct_offers set rewards_vendor_id=20;      

2 rows updated.

SQL> commit;

Commit complete.

Enable the session to view all the offers defined for the past, present and future time
 
SQL> exec dbms_flashback_archive.enable_at_valid_time('ALL');

PL/SQL procedure successfully completed.

SQL> select ACCT_REWARDS_ID,REWARDS_VENDOR_ID,ACCT_TYPE,ACCT_BRANCH_CODE,               
  2   to_char(OFFER_VALID_START,'DD-MON-YYYY') start_date, to_char(OFFER_VALID_END,'DD-MON-YYYY') end_date
  3   from acct_offers;

ACCT_REWARDS_ID REWARDS_VENDOR_ID ACCT_TYPE  ACCT_BRANCH_CODE START_DATE	   END_DATE
--------------- ----------------- ---------- ---------------- -------------------- --------------------
	   1001 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   1002 	       10 CURR			89898 02-MAR-2014	   02-APR-2014
	   2001 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			89898 02-APR-2014	   02-MAY-2014
	   1001 	       20 CURR			84372 02-MAY-2014	   02-JUN-2014
	   1002 	       20 CURR			84372 02-MAY-2014	   02-JUN-2014
	   2001 	       20 CURR			84372 02-APR-2014	   02-MAY-2014
	   2002 	       20 CURR			84372 02-APR-2014	   02-MAY-2014

8 rows selected.

 

 

Use the "drop period for" clause to disable the temporal validity for the table.

SQL> alter table acct_offers drop(period for OFFER_VALID);

Table altered.
 

 

Reference :

http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html

 

 
Cheers!

rajkiran

Copyright ©2018 Rajkiran Ghanta (deegeplanet.com)