创建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, 感谢原作者分享。
MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AMMySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand
MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AMToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith
MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AMToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters
MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AMMySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.
MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AMOptimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.
Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AMMastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.
Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AMChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic
MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AMBest practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
MinGW - Minimalist GNU for Windows
This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.
Dreamweaver Mac version
Visual web development tools
MantisBT
Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.
WebStorm Mac version
Useful JavaScript development tools
Zend Studio 13.0.1
Powerful PHP integrated development environment
