创建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, 感谢原作者分享。
声明
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系admin@php.cn
MySQL：初学者的基本技能Apr 18, 2025 am 12:24 AMMySQL适合初学者学习数据库技能。1.安装MySQL服务器和客户端工具。2.理解基本SQL查询，如SELECT。3.掌握数据操作：创建表、插入、更新、删除数据。4.学习高级技巧：子查询和窗口函数。5.调试和优化：检查语法、使用索引、避免SELECT*，并使用LIMIT。
MySQL：结构化数据和关系数据库Apr 18, 2025 am 12:22 AMMySQL通过表结构和SQL查询高效管理结构化数据，并通过外键实现表间关系。1.创建表时定义数据格式和类型。2.使用外键建立表间关系。3.通过索引和查询优化提高性能。4.定期备份和监控数据库确保数据安全和性能优化。
MySQL：解释的关键功能和功能Apr 18, 2025 am 12:17 AMMySQL是一个开源的关系型数据库管理系统，广泛应用于Web开发。它的关键特性包括：1.支持多种存储引擎，如InnoDB和MyISAM，适用于不同场景；2.提供主从复制功能，利于负载均衡和数据备份；3.通过查询优化和索引使用提高查询效率。
SQL的目的：与MySQL数据库进行交互Apr 18, 2025 am 12:12 AMSQL用于与MySQL数据库交互，实现数据的增、删、改、查及数据库设计。1）SQL通过SELECT、INSERT、UPDATE、DELETE语句进行数据操作；2）使用CREATE、ALTER、DROP语句进行数据库设计和管理；3）复杂查询和数据分析通过SQL实现，提升业务决策效率。
初学者的MySQL：开始数据库管理Apr 18, 2025 am 12:10 AMMySQL的基本操作包括创建数据库、表格，及使用SQL进行数据的CRUD操作。1.创建数据库：CREATEDATABASEmy_first_db;2.创建表格：CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY,titleVARCHAR(100)NOTNULL,authorVARCHAR(100)NOTNULL,published_yearINT);3.插入数据：INSERTINTObooks(title,author,published_year)VA
MySQL的角色：Web应用程序中的数据库Apr 17, 2025 am 12:23 AMMySQL在Web应用中的主要作用是存储和管理数据。1.MySQL高效处理用户信息、产品目录和交易记录等数据。2.通过SQL查询，开发者能从数据库提取信息生成动态内容。3.MySQL基于客户端-服务器模型工作，确保查询速度可接受。
mysql：构建您的第一个数据库Apr 17, 2025 am 12:22 AM构建MySQL数据库的步骤包括：1.创建数据库和表，2.插入数据，3.进行查询。首先，使用CREATEDATABASE和CREATETABLE语句创建数据库和表，然后用INSERTINTO语句插入数据，最后用SELECT语句查询数据。
MySQL：一种对数据存储的初学者友好方法Apr 17, 2025 am 12:21 AMMySQL适合初学者，因为它易用且功能强大。1.MySQL是关系型数据库，使用SQL进行CRUD操作。2.安装简单，需配置root用户密码。3.使用INSERT、UPDATE、DELETE、SELECT进行数据操作。4.复杂查询可使用ORDERBY、WHERE和JOIN。5.调试需检查语法，使用EXPLAIN分析查询。6.优化建议包括使用索引、选择合适数据类型和良好编程习惯。
热AI工具
Undresser.AI Undress
人工智能驱动的应用程序，用于创建逼真的裸体照片
AI Clothes Remover
用于从照片中去除衣服的在线人工智能工具。
Undress AI Tool
免费脱衣服图片
Clothoff.io
AI脱衣机
AI Hentai Generator
免费生成ai无尽的。
热门文章
R.E.P.O.能量晶体解释及其做什么（黄色晶体）
1 个月前By尊渡假赌尊渡假赌尊渡假赌
R.E.P.O.最佳图形设置
1 个月前By尊渡假赌尊渡假赌尊渡假赌
刺客信条阴影：贝壳谜语解决方案
3 周前ByDDD
Windows 11 KB5054979中的新功能以及如何解决更新问题
2 周前ByDDD
威尔R.E.P.O.有交叉游戏吗？
1 个月前By尊渡假赌尊渡假赌尊渡假赌
热工具
MinGW - 适用于 Windows 的极简 GNU
这个项目正在迁移到osdn.net/projects/mingw的过程中，你可以继续在那里关注我们。MinGW：GNU编译器集合（GCC）的本地Windows移植版本，可自由分发的导入库和用于构建本地Windows应用程序的头文件；包括对MSVC运行时的扩展，以支持C99功能。MinGW的所有软件都可以在64位Windows平台上运行。
Dreamweaver CS6
视觉化网页开发工具
WebStorm Mac版
好用的JavaScript开发工具
ZendStudio 13.5.1 Mac
功能强大的PHP集成开发环境
记事本++7.3.1
好用且免费的代码编辑器
