创建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 COLUMNOwner Name COLUMN_ID---------- ------------------------------ ---------TRAVEL USER_TIME_END 6TRAVEL USER_TIME_START 5TRAVEL NAME 4TRAVEL DEPTID 3TRAVEL SALARY 2TRAVEL EMPNO 1TRAVEL USER_TIME 7 ROWS selected. SQL> col NAME FOR a15SQL> col DEFAULT$ FOR a10SQL> col SPARE4 FOR a1SQL> col SPARE5 FOR a1SQL> col SPARE6 FOR a1 SQL> SELECT * FROM sys.col$ WHERE obj#=92087; OBJ# <strong style="color:transparent">本文来源gao@daima#com搞(%代@#码@网&</strong> 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 a20SQL> col TABLE_NAME FOR a15SQL> col CONSTRAINT_NAME FOR a20 USER_TIME_START ENABLED VALIDATEDSQL> col R_CONSTRAINT_NAME FOR a20SQL> @consSHOW 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 VALIDATEDTRAVEL 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 a25SQL> col OWNER FOR a10SQL> 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 4TRAVEL DEPTID 3TRAVEL SALARY 2TRAVEL EMPNO 1TRAVEL USER_TIMETRAVEL USER_TIME_ENDTRAVEL 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> @consSHOW 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 VALIDATEDTRAVEL USER_TIMEA43435 C USER_TIME_START ENABLED VALIDATED SQL>
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：oracle12c_Temporal Validity
转载请注明原文链接：oracle12c_Temporal Validity
