{"id":3991,"date":"2018-07-01T13:19:26","date_gmt":"2018-07-01T04:19:26","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=3991"},"modified":"2018-07-01T13:21:15","modified_gmt":"2018-07-01T04:21:15","slug":"temporal-validity-oracle-12c","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=3991","title":{"rendered":"Temporal Validity Oracle 12c"},"content":{"rendered":"

Temporal Validity<\/h2>\n

Temporal Validity\ub294 valid time dimension\uc744 \ud14c\uc774\ube14\uc5d0 \uc5f0\uacb0\uc2dc\ud0b4\uc73c\ub85c\uc368 \uc2dc\uac04 \uae30\uc900\uc73c\ub85c \ud574\ub2f9 \ub370\uc774\ud130\uc758 \uc720\ud6a8\ud55c \uc9c0 \uc5ec\ubd80\uc5d0 \ub530\ub77c \ub370\uc774\ud130\ub97c \ubcf4\uc774\uac8c \ud558\uac70\ub098 \ubcf4\uc774\uc9c0 \uc54a\uac8c \ud558\ub294 Oracle Database 12c New Feature\uc785\ub2c8\ub2e4.<\/p>\n

Sample \ud14c\uc774\ube14\uc744 \uac00\uc9c0\uace0 Temporal Validity\ub97c \uc2e4\uc2b5\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. [PDB1]\uc5d0\uc11c \uc9c4\ud589\ud569\ub2c8\ub2e4.<\/p>\n

\uba3c\uc800 [PDB1]\uc5d0 SYS \uc720\uc800\ub85c \uc811\uc18d\ud558\uc5ec \uc2e4\uc2b5 \uc720\uc800\uc778 HR \uc18c\uc720\uc758 \ud14c\uc2a4\ud2b8\uc6a9 \ud14c\uc774\ube14\uc744 \uc0dd\uc131\ud569\ub2c8\ub2e4. \uadf8\ub7f0 \ub2e4\uc74c \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\uc5d0 valid time dimension\uc744 \ucd94\uac00\ud569\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
[oracle@New-Features-12c ~]$ sqlplus sys\/oracle_4U@pdb1 as sysdba<\/span>
\n<\/span><\/p>\n

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 30 08:47:29 2015
\n<\/span><\/p>\n

Copyright (c) 1982, 2014, Oracle. All rights reserved.
\n<\/span><\/p>\n

Connected to:
\n<\/span><\/p>\n

Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production
\n<\/span><\/p>\n

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
\n<\/span><\/p>\n

and Real Application Testing options
\n<\/span><\/p>\n

SQL> create table hr.emp_temp as select employee_id,first_name,salary from hr.employees where rownum <= 10;<\/span>
\n<\/span><\/p>\n

Table created.
\n<\/span><\/p>\n

SQL> alter table hr.emp_temp add period for valid_time;
\n<\/span><\/span><\/p>\n

Table altered.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

valid time \uad00\ub828 \uce7c\ub7fc\uc740 Describe \uba85\ub839\uc5d0\uc11c\ub294 \ub098\ud0c0\ub098\uc9c0 \uc54a\uace0 \ucffc\ub9ac\uc5d0\uc11c \ud574\ub2f9 \uce7c\ub7fc\uc744 \uba85\uc2dc\ud588\uc744\ub54c\ub9cc \ub098\ud0c0\ub0a9\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> set lines 70
\n<\/span><\/span><\/p>\n

SQL> desc hr.emp_temp;
\n<\/span><\/span><\/p>\n

Name Null? Type
\n<\/span><\/p>\n

———————————– ——– ————————
\n<\/span><\/p>\n

EMPLOYEE_ID NUMBER(6)
\n<\/span><\/p>\n

FIRST_NAME VARCHAR2(20)
\n<\/span><\/p>\n

SALARY NUMBER(8,2)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\ud14c\uc774\ube14\uc5d0 valid time dimension\uc774 \ucd94\uac00\ub41c \uc0c1\ud0dc\uc5d0\uc11c valid-time \uce7c\ub7fc \uac12\uc744 \uc870\ud68c\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \ud604\uc7ac\ub294 \uc124\uc815\ub41c \ub0b4\uc6a9\uc774 \uc5c6\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp;<\/span>
\n<\/span><\/p>\n

FIRST_NAME Start End
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Steven
\n<\/span><\/p>\n

Neena
\n<\/span><\/p>\n

Lex
\n<\/span><\/p>\n

Alexander
\n<\/span><\/p>\n

Bruce
\n<\/span><\/p>\n

David
\n<\/span><\/p>\n

Valli
\n<\/span><\/p>\n

Diana
\n<\/span><\/p>\n

Nancy
\n<\/span><\/p>\n

Daniel
\n<\/span><\/p>\n

10 rows selected.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc774\uc81c valid-time \uce7c\ub7fc (valid_time_start, valid_time_end) \uc744 \uc544\ub798\uc640 \uac19\uc774 \uc5c5\ub370\uc774\ud2b8\ud558\uace0 \uc870\ud68c\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. valid-time \uc774 \uac01 \ub85c\uc6b0\uc5d0 \uc124\uc815\ub41c \uac83\uc744 \uc54c \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> update hr.emp_temp
\n<\/span><\/span><\/p>\n

set valid_time_start = to_date(‘1995\/06\/01′,’yyyy\/mm\/dd’), valid_time_end = to_date(‘2010\/09\/15′,’yyyy\/mm\/dd’)
\n<\/span><\/p>\n

where first_name in (‘Lex’,’Alexander’,’Bruce’,’David’,’Daniel’);<\/span>
\n<\/span><\/p>\n

5 rows updated.
\n<\/span><\/p>\n

SQL> update hr.emp_temp
\n<\/span><\/span><\/p>\n

set valid_time_start = to_date(‘1999\/08\/01′,’yyyy\/mm\/dd’), valid_time_end = to_date(‘2012\/03\/01′,’yyyy\/mm\/dd’)
\n<\/span><\/p>\n

where first_name in (‘Steven’,’Diana’);<\/span>
\n<\/span><\/p>\n

2 rows updated.
\n<\/span><\/p>\n

SQL> update hr.emp_temp
\n<\/span><\/span><\/p>\n

set valid_time_start = to_date(‘1998\/03\/20′,’yyyy\/mm\/dd’)
\n<\/span><\/p>\n

where first_name in (‘Neena’,’Nancy’,’Valli’);<\/span>
\n<\/span><\/p>\n

3 rows updated.
\n<\/span><\/p>\n

SQL> commit;
\n<\/span><\/span><\/p>\n

Commit complete.
\n<\/span><\/p>\n

SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp
\n<\/span><\/p>\n

order by 2;
\n<\/span><\/p>\n

FIRST_NAME Start End<\/span>
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Bruce 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

David 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Daniel 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Alexander 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Lex 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Valli 1998\/03\/20
\n<\/span><\/p>\n

Nancy 1998\/03\/20
\n<\/span><\/p>\n

Neena 1998\/03\/20
\n<\/span><\/p>\n

Diana 1999\/08\/01 2012\/03\/01
\n<\/span><\/p>\n

Steven 1999\/08\/01 2012\/03\/01
\n<\/span><\/p>\n

10 rows selected.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc774\uc81c Flashback \ucffc\ub9ac\ub97c \uc774\uc6a9\ud574\uc11c 2011\ub144 6\uc6d4 1\uc77c \ud604\uc7ac \uc720\ud6a8\ud55c \ub370\uc774\ud130\ub97c \uc870\ud68c\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp
\n<\/span><\/p>\n

as of period for valid_time to_date(‘2011\/06\/01′,’yyyy\/mm\/dd’)
\n<\/span><\/p>\n

order by 2;<\/span>
\n<\/span><\/p>\n

FIRST_NAME Start End
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Valli 1998\/03\/20
\n<\/span><\/p>\n

Nancy 1998\/03\/20
\n<\/span><\/p>\n

Neena 1998\/03\/20
\n<\/span><\/p>\n

Diana 1999\/08\/01 2012\/03\/01
\n<\/span><\/p>\n

Steven 1999\/08\/01 2012\/03\/01<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc774\ubc88\uc5d0\ub294 1995\ub144 9\uc6d4 1\uc77c\ubd80\ud130 1996\ub144 9\uc6d4 1\uc77c \uc0ac\uc774 \uae30\uac04 \ub3d9\uc548 \uc720\ud6a8\ud55c \ub370\uc774\ud130\ub97c VERSIONS \u2026 BETWEEN \uad6c\ubb38\uc744 \uc774\uc6a9\ud574\uc11c \uc870\ud68c\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp
\n<\/span><\/p>\n

versions period for valid_time
\n<\/span><\/p>\n

between to_date(‘1995\/09\/01′,’yyyy\/mm\/dd’) and to_date(‘1996\/09\/01′,’yyyy\/mm\/dd’)
\n<\/span><\/p>\n

order by 2;<\/span>
\n<\/span><\/p>\n

FIRST_NAME Start End
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Lex 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Alexander 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Daniel 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

David 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Bruce 1995\/06\/01 2010\/09\/15<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\uc774\uc81c dbms_flashback_archive.enable_at_valid_time \ud504\ub85c\uc2dc\uc800\ub97c \uc774\uc6a9\ud574\uc11c temporal support\uac00 \ud65c\uc131\ud654\ub41c \ud14c\uc774\ube14\uc758 \ub370\uc774\ud130\uc744 visibility\ub97c \uc870\uc815\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. \uba3c\uc800 \ud604\uc7ac \uc720\ud6a8\ud55c \ub370\uc774\ud130\ub9cc \ubcf4\uc774\uac8c \uc124\uc815\ud574\uc11c \ud655\uc778\ud574 \ubd05\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> exec dbms_flashback_archive.enable_at_valid_time(‘CURRENT’);<\/span>
\n<\/span><\/p>\n

PL\/SQL procedure successfully completed.
\n<\/span><\/p>\n

SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp
\n<\/span><\/p>\n

order by 2;
\n<\/span><\/p>\n

FIRST_NAME Start End
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Neena 1998\/03\/20
\n<\/span><\/p>\n

Nancy 1998\/03\/20
\n<\/span><\/p>\n

Valli 1998\/03\/20<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\ubaa8\ub4e0 \ub370\uc774\ud130\uac00 \ubcf4\uc774\ub3c4\ub85d \ubcc0\uacbd\ud558\uace0 \uc774\ub97c \ud655\uc778\ud569\ub2c8\ub2e4.<\/p>\n

\n\n\n<\/colgroup>\n\n\n
SQL> exec dbms_flashback_archive.enable_at_valid_time(‘ALL’);
\n<\/span><\/span><\/p>\n

PL\/SQL procedure successfully completed.
\n<\/span><\/p>\n

SQL> select first_name,
\n<\/span><\/span><\/p>\n

to_char(valid_time_start,’yyyy\/mm\/dd’) “Start”,
\n<\/span><\/p>\n

to_char(valid_time_end,’yyyy\/mm\/dd’) “End”
\n<\/span><\/p>\n

from hr.emp_temp
\n<\/span><\/p>\n

order by 2;
\n<\/span><\/p>\n

FIRST_NAME Start End
\n<\/span><\/p>\n

——————– ———- ———-
\n<\/span><\/p>\n

Bruce 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

David 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Daniel 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Alexander 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Lex 1995\/06\/01 2010\/09\/15
\n<\/span><\/p>\n

Valli 1998\/03\/20
\n<\/span><\/p>\n

Nancy 1998\/03\/20
\n<\/span><\/p>\n

Neena 1998\/03\/20
\n<\/span><\/p>\n

Diana 1999\/08\/01 2012\/03\/01
\n<\/span><\/p>\n

Steven 1999\/08\/01 2012\/03\/01
\n<\/span><\/p>\n

10 rows selected.
\n<\/span><\/p>\n

SQL> exit<\/span>
\n<\/span><\/p>\n

Disconnected from Oracle Database 12c EE Extreme Perf Release 12.1.0.2.0 – 64bit Production
\n<\/span><\/p>\n

With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
\n<\/span><\/p>\n

and Real Application Testing options<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

Temporal Validity Temporal Validity\ub294 valid time dimension\uc744 \ud14c\uc774\ube14\uc5d0 \uc5f0\uacb0\uc2dc\ud0b4\uc73c\ub85c\uc368 \uc2dc\uac04 \uae30\uc900\uc73c\ub85c \ud574\ub2f9 \ub370\uc774\ud130\uc758 \uc720\ud6a8\ud55c \uc9c0 \uc5ec\ubd80\uc5d0 \ub530\ub77c \ub370\uc774\ud130\ub97c \ubcf4\uc774\uac8c \ud558\uac70\ub098 \ubcf4\uc774\uc9c0 \uc54a\uac8c \ud558\ub294 Oracle Database 12c New Feature\uc785\ub2c8\ub2e4. Sample \ud14c\uc774\ube14\uc744 \uac00\uc9c0\uace0 Temporal Validity\ub97c \uc2e4\uc2b5\ud574 \ubcf4\uaca0\uc2b5\ub2c8\ub2e4. [PDB1]\uc5d0\uc11c \uc9c4\ud589\ud569\ub2c8\ub2e4. \uba3c\uc800 [PDB1]\uc5d0 SYS \uc720\uc800\ub85c \uc811\uc18d\ud558\uc5ec \uc2e4\uc2b5 \uc720\uc800\uc778 HR \uc18c\uc720\uc758 \ud14c\uc2a4\ud2b8\uc6a9 \ud14c\uc774\ube14\uc744 \uc0dd\uc131\ud569\ub2c8\ub2e4. \uadf8\ub7f0 \ub2e4\uc74c \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14\uc5d0 valid time […]<\/p>\n","protected":false},"author":1,"featured_media":2336,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[20],"tags":[1104],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/01\/Oracle-12c-logo-New.png?fit=499%2C302","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3991"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3991"}],"version-history":[{"count":2,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3991\/revisions"}],"predecessor-version":[{"id":3993,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3991\/revisions\/3993"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2336"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3991"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3991"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3991"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}