oracle12c_TemporalValidity

来源：动视网责编：小采时间：2020-11-09 13:08:31

oracle12c_TemporalValidity

oracle12c_TemporalValidity:创建Temporal Validity有两种方法一是自己指定列，一是让oracle自己指定手工指定 CREATE TABLE Temporal_ValidityEMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER, NAME VARCHAR2100, USER_TIME_START DATE, US

推荐度：

点击下载本文 文档为doc格式

导读oracle12c_TemporalValidity:创建Temporal Validity有两种方法一是自己指定列，一是让oracle自己指定手工指定 CREATE TABLE Temporal_ValidityEMPNO NUMBER, SALARY NUMBER, DEPTID NUMBER, NAME VARCHAR2100, USER_TIME_START DATE, US

创建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 920 920 20140526 22:41:37 20140526 22:41:37
 
SQL> SELECT * FROM sys.col$ WHERE obj#=920;
 
 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
---------- ---------- ---------- ------------ ---------- --------------- ---------- ---------- ------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------- ------------ --------------- ------------------ ---------- ---------- ---------- - - - ---------- ----------
 920 0 1 13 0 USER_TIME_START 181 13 0 6 0 1 32 0 0 0 6 0 0
 920 0 2 13 0 USER_TIME_END 181 13 0 6 0 2 32 0 0 0 6 0 0
 920 0 0 22 0 USER_TIME 2 22 0 0 0 9 430191669 3 65576 0 0 0 0 0 0
 920 1 3 22 0 EMPNO 2 22 0 0 4 0 0 0 0 0 0 0
 920 2 4 22 0 SALARY 2 22 0 0 5 0 0 0 0 0 0 0
 920 3 5 22 0 DEPTID 2 22 0 0 6 0 0 0 0 0 0 0
 920 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,00,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 00 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

示例2

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′‘.

示例3

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

示例4

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, 感谢原作者分享。

oracle12c_TemporalValidity

推荐度：

点击下载本文 文档为doc格式

标签：创建 oracle valid

热门焦点

oracle12c_TemporalValidity

oracle12c_TemporalValidity

oracle12c_TemporalValidity

最新推荐

猜你喜欢

热门推荐