oracle12c_TemporalValidity
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了oracle12c_TemporalValidity，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含19478字，纯文字阅读大概需要28分钟。
内容图文
创建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, 感谢原作者分享。
内容总结
以上是互联网集市为您收集整理的oracle12c_TemporalValidity全部内容，希望文章能够帮你解决oracle12c_TemporalValidity所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】