{"id":4713,"date":"2023-02-16T14:03:02","date_gmt":"2023-02-16T05:03:02","guid":{"rendered":"http:\/\/haisins.synology.me\/wordpress\/?p=4713"},"modified":"2023-02-16T14:23:14","modified_gmt":"2023-02-16T05:23:14","slug":"%ec%8b%9c%ed%80%80%ec%8a%a4-drop-%ec%82%ad%ec%a0%9c-%ec%8b%9c-%ec%8b%9c%ed%80%80%ec%8a%a4-%eb%b3%b5%ea%b5%ac-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4713","title":{"rendered":"\uc2dc\ud000\uc2a4 DROP \uc0ad\uc81c \uc2dc \uc2dc\ud000\uc2a4 \ubcf5\uad6c \ubc29\ubc95"},"content":{"rendered":"

Oracle 12c R2 \ubc84\uc804 \uc774\uc0c1 \ubcf5\uad6c \ubc29\ubc95 \uc785\ub2c8\ub2e4.<\/p>\n

\uc2e4\uc218\ub85c \uc2dc\ud000\uc2a4\ub97c DROP \ud588\uc744 \uacbd\uc6b0 \uc785\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

— \uc624\ud6c4 1\uc2dc\uc5d0 drop \uc2dc\ud000\uc2a4\ubb38\uc744 \uc2e4\ud589\ud55c \uacbd\uc6b0<\/p>\n

 <\/p>\n

select * from DBA_SEQUENCES as of timestamp(TO_DATE(‘2023\/01\/05 12:59:59′,’YYYY\/MM\/DD HH24:MI:SS’)) ;<\/p>\n

 <\/p>\n

\uc774\ub807\uac8c DBA \ubdf0 \uc5d0 Flashback \uae30\ub2a5\uc744 \uc811\ubaa9 \uc2dc\ucf1c \uc218\ud589 \ud558\uba74<\/p>\n

 <\/p>\n

\uc0ad\uc81c \uc774\uc804 \uc2dc\uc810\uc758 \uc2dc\ud000\uc2a4 Last number \ub9c8\uc9c0\ub9c9 \uc22b\uc790\ub97c \ucd94\ucd9c \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\uc774 \uac83\uc744 \uc774\uc6a9\ud574 \uc0ad\uc81c\ub41c \uc2dc\ud000\uc2a4\uba85 \uc73c\ub85c \uc0c8\ub85c\uc6b4 \uc2dc\ud000\uc2a4\ub97c\u00a0 \uc2dc\uc791\ud558\ub294 \uc22b\uc790\ub97c \uc0ad\uc81c \uc2dc\uc810 \uc2dc\ud000\uc2a4 \ub9c8\uc9c0\ub9c9 \uc22b\uc790\ub85c \ud558\uc5ec \ub9cc\ub4e4\uba74 \ubc88\ud638\uac00 \uc911\ubcf5\ub418\uc9c0 \uc54a\uace0 \ubcf5\uad6c\uac00 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\ub2e8 LAST number\ub294 cache \uac12 \ub9cc\ud07c \uc120 \ud655\ubcf4\ub418\uc5b4\uc11c \uc911\uac04 \ubc88\ud638\uac00 \ub760\uc5b4\uc9c4 \uc0c1\ud0dc \uc785\ub2c8\ub2e4.<\/p>\n

 <\/p>\n

\uc624\ub77c\ud074(ORACLE) \ub370\uc774\ud130\ub97c\u00a0TIMESTAMP\ub97c \uc0ac\uc6a9\ud558\uc5ec\u00a0\ubcf5\uad6c<\/h2>\n

\ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \uc774\uc6a9\ud558\ub2e4\ubcf4\uba74\u00a0\uc2e4\uc218\ub85c \ub370\uc774\ud130\ub97c \uc0ad\uc81c<\/b>\ud558\uac70\ub098,\u00a0\uc798\ubabb \uc5c5\ub370\uc774\ud2b8<\/b>\ub97c \ud558\ub294 \uacbd\uc6b0\uac00 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

commit\uc744 \ud558\uae30\uc804\uc774\ub77c\uba74 \uc0c1\uad00\uc774 \uc5c6\uc2b5\ub2c8\ub2e4.<\/p>\n

\ud558\uc9c0\ub9cc commit\uc744 \ud55c \uc774\ud6c4\uc5d0 \ubb38\uc81c\ub97c \ud30c\uc545\ud558\uc5ec \ub370\uc774\ud130\ub97c \ubcf5\uad6c\ud574\uc57c \ud558\ub294 \uacbd\uc6b0\uac00 \uc0dd\uae41\ub2c8\ub2e4.<\/p>\n

\uc774\ub7f4\ub54c ORACLE \uc758\u00a0TIMESTAMP\ub97c \uc774\uc6a9<\/b>\ud558\uba74 \ub370\uc774\ud130\ub97c \ubcf5\uad6c \ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

TIMESTAMP \uc0ac\uc6a9\uc870\uac74<\/b><\/h3>\n

oracle 9i \uc774\uc0c1\ubd80\ud130 \uc9c0\uc6d0<\/p>\n

DB\uc758 \uc124\uc815\uc5d0 \ub530\ub77c\uc11c\u00a0\uc2dc\uac04\uc774 \uc624\ub798\uc9c0\ub09c \ub370\uc774\ud130\ub294 TIMESTAMP\ub85c \ubcf5\uad6c\uac00 \ubd88\uac00\ub2a5\ud569\ub2c8\ub2e4.<\/b><\/p>\n

TIMESTAMP \uc0ac\uc6a9\ubc95<\/b><\/h3>\n

\ub2e8\uc704\ub294 SECOND, MINUT, HOUR, DAY \ub85c \uc9c0\uc815\ud574\uc11c \uc0ac\uc6a9\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n

SYSTIMESTAMP \uc5d0\uc11c \uc124\uc815\ud55c \uc2dc\uac04\uc744 \uc785\ub825\ud558\uc5ec \ub370\uc774\ud130\ub97c \ubcf4\uc5ec\uc90d\ub2c8\ub2e4.<\/p>\n

-- 10\ucd08\uc804 \ub370\uc774\ud130 \uc870\ud68c<\/span>\u00a0SELECT<\/span>  *  FROM<\/span> \ud14c\uc774\ube14 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'10'<\/span>\u00a0SECOND<\/span>)\r\nWHERE<\/span> \uceec\ub7fc = 'A'<\/span>; -- \ud544\uc694\uc5d0 \ub530\ub77c\uc11c \uc870\uac74\ubb38<\/span>\u00a0\u00a0-- 10\ubd84\uc804 \ub370\uc774\ud130 \uc870\ud68c<\/span>\u00a0SELECT<\/span>  *  FROM<\/span> \ud14c\uc774\ube14 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'10'<\/span>\u00a0MINUTE<\/span>)\r\n \r\n-- 3\uc2dc\uac04\uc804 \ub370\uc774\ud130 \uc870\ud68c <\/span>\u00a0SELECT<\/span>  *  FROM<\/span> \ud14c\uc774\ube14 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'3'<\/span>\u00a0HOUR<\/span>)\r\n\r\n-- 1\uc77c\uc804 \ub370\uc774\ud130 \uc870\ud68c <\/span>\u00a0SELECT<\/span>  *  FROM<\/span> \ud14c\uc774\ube14 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'1'<\/span>\u00a0DAY<\/span>)\r\n\r\n-- \ud2b9\uc815\uc2dc\uac04 \uae30\uc900\uc73c\ub85c \ub370\uc774\ud130 \uc870\ud68c<\/span>\u00a0SELECT<\/span> * FROM<\/span> USER_TB AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(TO_DATE<\/span>('20200605000000'<\/span>, 'YYYYMMDDHH24MISS'<\/span>));<\/code><\/pre>\n
TIMESTAMP \ub370\uc774\ud130 \uc27d\uac8c \ube44\uad50\ud558\uae30<\/b><\/h3>\n
\uc5b4\ub5a4 \ub370\uc774\ud130\uac00 \ubcc0\uacbd\uc774 \ub418\uc5c8\ub294\uc9c0 \ud655\uc778\ud560 \uacbd\uc6b0<\/p>\n
\uc544\ub798\uc640\u00a0\uac19\uc774 \ube44\uad50\ud558\uc2dc\uba74\uc11c \ubcf4\uba74 \ud655\uc778<\/b>\ud558\uae30\uac00 \ud3b8\ud569\ub2c8\ub2e4.<\/p>\n
\ud604\uc7ac \ub370\uc774\ud130\uc640 \ube44\uad50\ud560 \ub370\uc774\ud130\ub97c\u00a0UNION ALL<\/b>\ub85c \ubb36\uc5b4\uc11c \ubcf4\uae30<\/p>\n
-- GOODS_NM \uc774 \uc120\ud48d\uae30\uc778 \ub370\uc774\ud130\ub97c \ud604\uc7ac \ub370\uc774\ud130\uc640 10\ubd84\uc804 \ub370\uc774\ud130 \ube44\uad50<\/span>\u00a0\u00a0SELECT<\/span> * FROM<\/span> TEMP_1018 where<\/span> GOODS_NM = '\uc120\ud48d\uae30'<\/span>\u00a0UNION<\/span>\u00a0ALL<\/span>\u00a0\u00a0SELECT<\/span>  *  FROM<\/span> TEMP_1018 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'10'<\/span>\u00a0MINUTE<\/span>) where<\/span> GOODS_NM = '\uc120\ud48d\uae30'<\/span>;\r\n\r\n-- 10\ubd84\uc804\uc5d0\ub294 GOODS_QTY \uac00 77\uac1c \uc778\uac83\uc744 \ud655\uc778<\/span><\/code><\/pre>\n
<\/span><\/figure>\n
TIMESTAMP \ub370\uc774\ud130 \uc0dd\uc131, \uc218\uc815 \ubc29\ubc95<\/b><\/h3>\n
-- select insert \ub97c \uc774\uc6a9\ud558\uc5ec \uc0ad\uc81c\ub41c \ub370\uc774\ud130 \ucc98\ub9ac<\/span>\u00a0INSERT<\/span>\u00a0INTO<\/span> TEMP_1018\r\nSELECT<\/span>  *  FROM<\/span> TEMP_1018 AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'10'<\/span>\u00a0MINUTE<\/span>) where<\/span> GOODS_NM = '\uc120\ud48d\uae30'<\/span>;\r\n\r\n\r\n-- select update \ub97c \uc774\uc6a9\ud558\uc5ec \ubcc0\uacbd\ub41c \ub370\uc774\ud130 \ucc98\ub9ac<\/span>\u00a0UPDATE<\/span> TEMP_1018 a\r\nSET<\/span> \r\na.GOODS_QTY = (SELECT<\/span> B.GOODS_QTY FROM<\/span> TEMP_1018 B AS<\/span>\u00a0OF<\/span>\u00a0TIMESTAMP<\/span>(SYSTIMESTAMP-INTERVAL<\/span>\u00a0'10'<\/span>\u00a0MINUTE<\/span>) where<\/span> a.GOODS_NM = b.GOODS_NM and<\/span>  b.GOODS_NM = '\uc120\ud48d\uae30'<\/span>)\r\nWHERE<\/span> a.GOODS_NM ='\uc120\ud48d\uae30'<\/span>    <\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
Oracle 12c R2 \ubc84\uc804 \uc774\uc0c1 \ubcf5\uad6c \ubc29\ubc95 \uc785\ub2c8\ub2e4. \uc2e4\uc218\ub85c \uc2dc\ud000\uc2a4\ub97c DROP \ud588\uc744 \uacbd\uc6b0 \uc785\ub2c8\ub2e4.   — \uc624\ud6c4 1\uc2dc\uc5d0 drop \uc2dc\ud000\uc2a4\ubb38\uc744 \uc2e4\ud589\ud55c \uacbd\uc6b0   select * from DBA_SEQUENCES as of timestamp(TO_DATE(‘2023\/01\/05 12:59:59′,’YYYY\/MM\/DD HH24:MI:SS’)) ;   \uc774\ub807\uac8c DBA \ubdf0 \uc5d0 Flashback \uae30\ub2a5\uc744 \uc811\ubaa9 \uc2dc\ucf1c \uc218\ud589 \ud558\uba74   \uc0ad\uc81c \uc774\uc804 \uc2dc\uc810\uc758 \uc2dc\ud000\uc2a4 Last number \ub9c8\uc9c0\ub9c9 \uc22b\uc790\ub97c \ucd94\ucd9c \ud560 […]<\/p>\n","protected":false},"author":1,"featured_media":4141,"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":[14],"tags":[1265,692,693,1264,1266,695],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/08\/oracletips.jpg?fit=650%2C361","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4713"}],"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=4713"}],"version-history":[{"count":3,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4713\/revisions"}],"predecessor-version":[{"id":4720,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4713\/revisions\/4720"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/4141"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4713"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}