oracle12c_Temporal Validity
程序员文章站
2022-05-21 11:12:54
...
创建Temporal Validity有两种方法一是自己指定列,一是让oracle自己指定 手工指定 CREATE TABLE Temporal_ValidityEMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER, NAME VARCHAR2100, USER_TIME_START DATE, USER_TIME_END DATE, PERIOD FOR USER_TIME USER_TI
创建Temporal Validity有两种方法一是自己指定列,一是让oracle自己指定
- 手工指定
CREATE TABLE Temporal_Validity (EMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER, NAME VARCHAR2(100), USER_TIME_START DATE, USER_TIME_END DATE, PERIOD FOR USER_TIME (USER_TIME_START, USER_TIME_END)); SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME=UPPER('Temporal_Validity'); : TEMPORAL_VALIDITY TABLE COLUMN Owner Name COLUMN_ID ---------- ------------------------------ --------- TRAVEL USER_TIME_END 6 TRAVEL USER_TIME_START 5 TRAVEL NAME 4 TRAVEL DEPTID 3 TRAVEL SALARY 2 TRAVEL EMPNO 1 TRAVEL USER_TIME 7 ROWS selected. SQL> col NAME FOR a15 SQL> col DEFAULT$ FOR a10 SQL> col SPARE4 FOR a1 SQL> col SPARE5 FOR a1 SQL> col SPARE6 FOR a1 SQL> SELECT * FROM sys.col$ WHERE obj#=92087; OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 S S S SPARE7 SPARE8 ---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ---------- 92087 0 0 22 0 USER_TIME 2 22 0 0 0 9 430120635 1 65576 0 0 0 0 0 0 92087 1 1 22 0 EMPNO 2 22 0 0 2 0 0 0 0 0 0 0 92087 2 2 22 0 SALARY 2 22 0 0 3 0 0 0 0 0 0 0 92087 3 3 22 0 DEPTID 2 22 0 0 4 0 0 0 0 0 0 0 92087 4 4 100 0 NAME 1 100 0 0 5 0 873 1 0 0 0 100 92087 5 5 7 0 USER_TIME_START 12 7 0 0 6 0 0 0 0 0 0 0 92087 6 6 7 0 USER_TIME_END 12 7 0 0 7 0 0 0 0 0 0 0 7 ROWS selected. SQL> col COLUMN_NAME FOR a20 SQL> col TABLE_NAME FOR a15 SQL> col CONSTRAINT_NAME FOR a20 USER_TIME_START ENABLED VALIDATED SQL> col R_CONSTRAINT_NAME FOR a20 SQL> @cons SHOW constraints ON TABLE %TEMPORAL_VALIDITY%... OWNER TABLE_NAME CONSTRAINT_NAME CO R_CONSTRAINT_NAME COLUMN_NAME POSITION STATUS VALIDATED ------------------------------ --------------- -------------------- -- -------------------- ------------------------------ ---------- ---------------- -------------------------- TRAVEL TEMPORAL_VALIDI USER_TIMEA31EBB C USER_TIME ENABLED VALIDATED TY TRAVEL USER_TIMEA31EBB C USER_TIME_END ENABLED VALIDATED TRAVEL USER_TIMEA31EBB C USER_TIME_START ENABLED VALIDATED SQL> SQL> @DESC TEMPORAL_VALIDITY Name NULL? TYPE ------------------------------- -------- ---------------------------- 1 EMPNO NUMBER 2 SALARY NUMBER 3 DEPTID NUMBER 4 NAME VARCHAR2(100) 5 USER_TIME_START DATE 6 USER_TIME_END DATE 手工指定是正常列,可以通过SELECT * 来查看
SQL> SQL> CREATE TABLE TEMPORAL_VALIDITY2 2 ( empno NUMBER, salary NUMBER, deptid NUMBER, name VARCHAR2(100), 3 PERIOD FOR user_time); TABLE created. SQL> @DESC TEMPORAL_VALIDITY2 Name NULL? TYPE ------------------------------- -------- ---------------------------- 1 EMPNO NUMBER 2 SALARY NUMBER 3 DEPTID NUMBER 4 NAME VARCHAR2(100) SQL> SQL> col TABLE_NAME FOR a25 SQL> col OWNER FOR a10 SQL> SELECT OWNER,TABLE_NAME,COLUMN_NAME,COLUMN_ID FROM DBA_TAB_COLS WHERE TABLE_NAME=UPPER('Temporal_Validity2'); OWNER TABLE_NAME COLUMN_NAME COLUMN_ID ---------- ------------------------- -------------------- ---------- TRAVEL TEMPORAL_VALIDITY2 NAME 4 TRAVEL DEPTID 3 TRAVEL SALARY 2 TRAVEL EMPNO 1 TRAVEL USER_TIME TRAVEL USER_TIME_END TRAVEL USER_TIME_START 7 ROWS selected. SQL> SQL> @o TEMPORAL_VALIDITY2 owner object_name object_type STATUS OID D_OID CREATED LAST_DDL_TIME ------------------------- ------------------------------ ------------------ --------- ---------- ---------- ----------------- ----------------- TRAVEL TEMPORAL_VALIDITY2 TABLE VALID 92089 92089 20140526 22:41:37 20140526 22:41:37 SQL> SELECT * FROM sys.col$ WHERE obj#=92089; OBJ# COL# SEGCOL# SEGCOLLENGTH OFFSET NAME TYPE# LENGTH FIXEDSTORAGE PRECISION# SCALE NULL$ DEFLENGTH DEFAULT$ INTCOL# PROPERTY CHARSETID CHARSETFORM EVALEDITION# UNUSABLEBEFORE# UNUSABLEBEGINNING# SPARE1 SPARE2 SPARE3 S S S SPARE7 SPARE8 ---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ---------- 92089 0 1 13 0 USER_TIME_START 181 13 0 6 0 1 32 0 0 0 6 0 0 92089 0 2 13 0 USER_TIME_END 181 13 0 6 0 2 32 0 0 0 6 0 0 92089 0 0 22 0 USER_TIME 2 22 0 0 0 9 430191669 3 65576 0 0 0 0 0 0 92089 1 3 22 0 EMPNO 2 22 0 0 4 0 0 0 0 0 0 0 92089 2 4 22 0 SALARY 2 22 0 0 5 0 0 0 0 0 0 0 92089 3 5 22 0 DEPTID 2 22 0 0 6 0 0 0 0 0 0 0 92089 4 6 100 0 NAME 1 100 0 0 7 0 873 1 0 0 0 100 7 ROWS selected. SQL> @cons SHOW constraints ON TABLE %TEMPORAL_VALIDITY2%... OWNER TABLE_NAME CONSTRAINT_NAME CO R_CONSTRAINT_NAME COLUMN_NAME POSITION STATUS VALIDATED ------------------------------ ------------------------- -------------------- -- -------------------- ------------------------------ ---------- ---------------- -------------------------- TRAVEL TEMPORAL_VALIDITY2 USER_TIMEA43435 C USER_TIME_END ENABLED VALIDATED TRAVEL USER_TIMEA43435 C USER_TIME_START ENABLED VALIDATED SQL>
系统自动创建了2个隐藏列USER_TIME_START,USER_TIME_END
插入数据
SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (101,1900,90,'ADAM',to_date('2000-01-01', 'yyyy-mm-dd') ,to_date('2004-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (102,2900,90,'ADAM',to_date('2001-01-01', 'yyyy-mm-dd') ,to_date('2005-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (103,3900,90,'ADAM',to_date('2002-01-01', 'yyyy-mm-dd') ,to_date('2006-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (104,4900,90,'ADAM',to_date('2003-01-01', 'yyyy-mm-dd') ,to_date('2007-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (105,5900,90,'ADAM',to_date('2004-01-01', 'yyyy-mm-dd') ,to_date('2008-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (106,6900,90,'ADAM',to_date('2005-01-01', 'yyyy-mm-dd') ,to_date('2009-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (107,7900,90,'ADAM',to_date('2006-01-01', 'yyyy-mm-dd') ,to_date('2010-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (108,8900,90,'ADAM',to_date('2007-01-01', 'yyyy-mm-dd') ,to_date('2011-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (109,9900,90,'ADAM',to_date('2008-01-01', 'yyyy-mm-dd') ,to_date('2012-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (110,1900,90,'ADAM',to_date('2009-01-01', 'yyyy-mm-dd') ,to_date('2014-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (111,2900,90,'ADAM',to_date('2010-01-01', 'yyyy-mm-dd') ,to_date('2011-05-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (112,3900,90,'ADAM',to_date('2011-01-01', 'yyyy-mm-dd') ,to_date('2011-08-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (113,4900,90,'ADAM',to_date('2012-01-01', 'yyyy-mm-dd') ,to_date('2013-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (114,5900,90,'ADAM',to_date('2013-01-01', 'yyyy-mm-dd') ,to_date('2014-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (115,6900,90,'ADAM',to_date('2014-01-01', 'yyyy-mm-dd') ,to_date('2015-01-01', 'yyyy-mm-dd')); 1 ROW created. SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (116,7900,90,'ADAM',to_date('2002-04-01', 'yyyy-mm-dd') ,to_date('2002-06-01', 'yyyy-mm-dd'));om 2 SQL> INSERT INTO TEMPORAL_VALIDITY2 (empno , salary , deptid , name , USER_TIME_START, USER_TIME_END) VALUES (116,7900,90,'ADAM',to_date('2002-04-01', 'yyyy-mm-dd') ,to_date('2002-06-01', 'yyyy-mm-dd')) 2 ; 1 ROW created. SQL> commit; Commit complete. SQL> SELECT * FROM TEMPORAL_VALIDITY2; EMPNO SALARY DEPTID NAME ---------- ---------- ---------- --------------- 101 1900 90 ADAM 101 1900 90 ADAM 102 2900 90 ADAM 103 3900 90 ADAM 104 4900 90 ADAM 105 5900 90 ADAM 106 6900 90 ADAM 107 7900 90 ADAM 108 8900 90 ADAM 109 9900 90 ADAM 110 1900 90 ADAM 111 2900 90 ADAM 112 3900 90 ADAM 113 4900 90 ADAM 114 5900 90 ADAM 115 6900 90 ADAM 116 7900 90 ADAM SQL> SELECT NAME, 2 to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd') 3 FROM TEMPORAL_VALIDITY2 ORDER BY 2; NAME TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN --------------- -------------------- -------------------- ADAM 2000-01-01 2011-12-31 ADAM 2000-01-01 2004-01-01 ADAM 2001-01-01 2005-01-01 ADAM 2002-01-01 2006-01-01 ADAM 2002-04-01 2002-06-01 ADAM 2003-01-01 2007-01-01 ADAM 2004-01-01 2008-01-01 ADAM 2005-01-01 2009-01-01 ADAM 2006-01-01 2010-01-01 ADAM 2007-01-01 2011-01-01 ADAM 2008-01-01 2012-01-01 ADAM 2009-01-01 2014-01-01 ADAM 2010-01-01 2011-05-01 ADAM 2011-01-01 2011-08-01 ADAM 2012-01-01 2013-01-01 ADAM 2013-01-01 2014-01-01 ADAM 2014-01-01 2015-01-01
- 示例1
SQL> SELECT NAME, 2 to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd') 3 FROM TEMPORAL_VALIDITY2 AS OF PERIOD FOR USER_TIME 4 to_date('2004-01-01', 'yyyy-mm-dd') ORDER BY 2; NAME TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN --------------- -------------------- -------------------- ADAM 2000-01-01 2011-12-31 ADAM 2001-01-01 2005-01-01 ADAM 2002-01-01 2006-01-01 ADAM 2003-01-01 2007-01-01 ADAM 2004-01-01 2008-01-01
The query displays all whose
SQL> SELECT NAME, 2 to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd') 3 FROM TEMPORAL_VALIDITY2 versions PERIOD FOR USER_TIME 4 BETWEEN to_date('2004-01-01', 'yyyy-mm-dd') 5 AND to_date('2008-01-01', 'yyyy-mm-dd') 6 ORDER BY 2; NAME TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN --------------- -------------------- -------------------- ADAM 2000-01-01 2011-12-31 ADAM 2001-01-01 2005-01-01 ADAM 2002-01-01 2006-01-01 ADAM 2003-01-01 2007-01-01 ADAM 2004-01-01 2008-01-01 ADAM 2005-01-01 2009-01-01 ADAM 2006-01-01 2010-01-01 ADAM 2007-01-01 2011-01-01 ADAM 2008-01-01 2012-01-01
The query displays all whose USER_TIME_START is less than or equal to ‘2004-01-01′ and USER_TIME_END greater than or equal to ‘2008-01-01′‘.
SQL> EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('CURRENT') PL/SQL PROCEDURE successfully completed. SQL> SELECT NAME, 2 to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd') 3 FROM TEMPORAL_VALIDITY2 ORDER BY 2; NAME TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN --------------- -------------------- -------------------- ADAM 2014-01-01 2015-01-01
SQL> EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_AT_VALID_TIME('ALL') PL/SQL PROCEDURE successfully completed. SQL> SELECT NAME, 2 to_char(USER_TIME_START, 'yyyy-mm-dd'), to_char(USER_TIME_END, 'yyyy-mm-dd') 3 FROM TEMPORAL_VALIDITY2 ORDER BY 2; NAME TO_CHAR(USER_TIME_ST TO_CHAR(USER_TIME_EN --------------- -------------------- -------------------- ADAM 2000-01-01 2011-12-31 ADAM 2000-01-01 2004-01-01 ADAM 2001-01-01 2005-01-01 ADAM 2002-01-01 2006-01-01 ADAM 2002-04-01 2002-06-01 ADAM 2003-01-01 2007-01-01 ADAM 2004-01-01 2008-01-01 ADAM 2005-01-01 2009-01-01 ADAM 2006-01-01 2010-01-01 ADAM 2007-01-01 2011-01-01 ADAM 2008-01-01 2012-01-01 ADAM 2009-01-01 2014-01-01 ADAM 2010-01-01 2011-05-01 ADAM 2011-01-01 2011-08-01 ADAM 2012-01-01 2013-01-01 ADAM 2013-01-01 2014-01-01 ADAM 2014-01-01 2015-01-01 17 ROWS selected.
原文地址:oracle12c_Temporal Validity, 感谢原作者分享。