oracle12c_Temporal Validity

创建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

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