{"id":727,"date":"2012-04-07T23:45:43","date_gmt":"2012-04-07T15:45:43","guid":{"rendered":"https:\/\/yangtingkun.net\/?p=727"},"modified":"2012-04-12T00:40:31","modified_gmt":"2012-04-11T16:40:31","slug":"%e9%80%9a%e8%bf%87%e6%95%b0%e6%8d%ae%e5%ba%93%e9%93%be%e6%8c%87%e5%ae%9aas-of-scn%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/yangtingkun.net\/?p=727","title":{"rendered":"\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u6307\u5b9aAS OF SCN\u8bed\u53e5"},"content":{"rendered":"<p>\u4e00\u76f4\u4e0d\u786e\u5b9a\u95ea\u56de\u67e5\u8be2\u7684AS OF\u8bed\u53e5\u662f\u5426\u53ef\u4ee5\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u5bf9\u8fdc\u7aef\u6570\u636e\u5e93\u751f\u6548\uff0c\u800c\u7531\u4e8e\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u67e5\u8be2\u4f1a\u4f7f\u4e24\u4e2a\u6570\u636e\u5e93\u7684SCN\u540c\u6b65\uff0c\u4f7f\u5f97\u8fd9\u4e2a\u95ee\u9898\u96be\u4ee5\u6d4b\u8bd5\u3002<br \/>\n\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u67e5\u8be2\u4f1a\u5bfc\u81f4\u6e90\u548c\u76ee\u6807\u5e93SCN\u540c\u6b65\uff1a<a href=\"https:\/\/yangtingkun.net\/?p=722\">https:\/\/yangtingkun.net\/?p=722<\/a><br \/>\n\u867d\u7136Oracle\u4f1a\u5728\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u6267\u884c\u67e5\u8be2\u6216DML\u64cd\u4f5c\u65f6\u540c\u6b65\u6570\u636e\u5e93\u7684SCN\uff0c\u4f46\u662f\u6307\u5b9a\u7684SCN\u662f\u672c\u5730\u7684\u8fd8\u662f\u8fdc\u7aef\u7684\u8fd8\u662f\u6709\u610f\u4e49\u7684\u3002<br \/>\n\u4e3a\u4e86\u8bf4\u660e\u8fd9\u79cd\u60c5\u51b5\uff0c\u521b\u5efa\u4e00\u4e2aJOB\uff0c\u5b9a\u671f\u8fdb\u884c\u6570\u636e\u7684\u4fee\u6539\uff0c\u4ee5\u5feb\u901f\u63d0\u9ad8\u4e00\u4e2a\u6570\u636e\u5e93\u7684SCN\uff1a<\/p>\n<pre lang='SQL'>SQL> select dbid, name from v$database;\r\nDBID NAME\r\n---------- ---------\r\n 452011237 ORAT2\r\nSQL> select count(*) from t;\r\nCOUNT(*)\r\n----------\r\n731791\r\nSQL> begin\r\n2 for i in 1..100000 loop\r\n3 insert into t values (i, 'y');\r\n4 commit;\r\n5 end loop;\r\n6 end;\r\n7 \/\r\nPL\/SQL procedure successfully completed.<\/pre>\n<p>\u5728\u53e6\u4e00\u4e2a\u6570\u636e\u5e93\u4e2d\u5c06\u7cfb\u7edf\u65f6\u95f4\u548cSCN\u7684\u5bf9\u5e94\u5173\u7cfb\u4fdd\u5b58\uff1a<\/p>\n<pre lang='SQL'>SQL> select dbid, name from v$database;\r\n      DBID NAME\r\n---------- ---------\r\n1299676637 ORCL\r\nSQL> CREATE DATABASE LINK TEST CONNECT TO TEST IDENTIFIED BY TEST using '192.168.0.19:1524\/orat2';   \r\nDatabase link created.\r\nSQL> declare\r\n2 v_job number;\r\n3 begin\r\n4 dbms_job.submit(v_job, 'begin insert into t_scn values (sysdate, dbms_flashback.get_system_change_number); end;', \r\n5 sysdate, 'sysdate + 1\/86400');\r\n6 commit;\r\n7 end;\r\n8 \/\r\nPL\/SQL procedure successfully completed.<\/pre>\n<p>\u68c0\u67e5\u63d2\u5165\u7684SCN\u4fe1\u606f\uff1a<\/p>\n<pre lang='SQL'>SQL> select * from t_scn;\r\nTIME                    SCN\r\n------------------- -------\r\n2012-04-06 18:38:39 5098024\r\n2012-04-06 18:38:44 5098031\r\n2012-04-06 18:38:49 5098035\r\n2012-04-06 18:38:54 5098040\r\n2012-04-06 18:38:59 5098045\r\n.\r\n.\r\n.\r\n2012-04-06 18:42:49 5098266\r\n2012-04-06 18:42:54 5098271\r\n2012-04-06 18:42:59 5098276\r\n53 rows selected.\r\nSQL> select dbid, name, current_scn from v$database@test;\r\nDBID NAME      CURRENT_SCN\r\n---------- --------- -----------\r\n 452011237 ORAT2         5173454\r\nSQL> select * from t_scn;\r\nTIME                    SCN\r\n------------------- -------\r\n2012-04-06 18:38:39 5098024\r\n2012-04-06 18:38:44 5098031\r\n2012-04-06 18:38:49 5098035\r\n2012-04-06 18:38:54 5098040\r\n.\r\n.\r\n.\r\n2012-04-06 18:43:04 5098280\r\n2012-04-06 18:43:09 5098285\r\n2012-04-06 18:43:14 5173457\r\n56 rows selected.<pre>\r\n\u53ef\u4ee5\u770b\u5230\uff0c\u5f53\u524d\u6570\u636e\u5e93\u7531\u4e8e\u6267\u884c\u4e86\u8fdc\u7aef\u7684\u67e5\u8be2\uff0c\u5bfc\u81f4\u5f53\u524d\u7684\u6570\u636e\u5e93SCN\u548c\u8fdc\u7aefSCN\u540c\u6b65\u3002\u7531\u4e8e\u8fdc\u7aef\u901a\u8fc7PL\/SQL\u8fd0\u884c\u5927\u91cf\u7684\u4e8b\u52a1\uff0c\u56e0\u6b64SCN\u6da8\u5e45\u5f88\u5feb\uff0c\u800c\u5f53\u524d\u6570\u636e\u5e93\u6ca1\u6709\u64cd\u4f5c\u8fdb\u884c\uff0c\u57fa\u672c\u4e0aSCN\u4ee5\u6bcf\u79d21\u4e2a\u7684\u901f\u5ea6\u589e\u52a0\u3002\u5728\u8fd0\u884c\u4e86\u67e5\u8be2\u540e\uff0cSCN\u5219\u76f4\u63a5\u589e\u52a0\u4e8675000\u3002\r\n\u56e0\u6b64\u5bf9\u4e8e\u5f53\u524d\u6570\u636e\u5e93\u7684\u800c\u8a00\uff0cSCN\u662f\u4e0d\u8fde\u7eed\u7684\uff0c\u5bf9\u4e8e\u5f53\u524d\u5e93\u800c\u8a00\uff0c5098286\u548c5173456\u4e4b\u95f4\u7684\u503c\u6ca1\u6709\u4efb\u4f55\u533a\u522b\uff0c\u4e0b\u9762\u5c1d\u8bd5\u901a\u8fc7SCN\u67e5\u8be2\u8fdc\u7aef\u6570\u636e\u5e93\u4e2d\u7684\u8bb0\u5f55\uff1a\r\n<pre lang='SQL'>SQL> select count(*) from t@test as of scn 5098286;\r\nCOUNT(*)\r\n----------\r\n    731976\r\nSQL> select count(*) from t@test as of scn 5173457;\r\nCOUNT(*)\r\n----------\r\n    804664\r\nSQL> select count(*) from t@test as of scn 5100000;\r\nCOUNT(*)\r\n----------\r\n    733675\r\nSQL> select count(*) from t@test as of scn 5140000;\r\nCOUNT(*)\r\n----------\r\n    772940<\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\u901a\u8fc7\u6307\u5b9aSCN\u53ef\u4ee5\u67e5\u8be2\u5230\u8fdc\u7aefT\u8868\u4e0d\u540c\u65f6\u523b\u7684\u8bb0\u5f55\u6570\uff0c\u800c\u8fd9\u4e9b\u6307\u5b9a\u7684SCN\u662f\u5f53\u524d\u6570\u636e\u5e93\u8df3\u8fc7\u7684SCN\uff0c\u56e0\u6b64\u53ef\u4ee5\u786e\u5b9a\uff0cAS OF SCN\u8bed\u53e5\u6307\u5b9a\u7684SCN\u662f\u9488\u5bf9\u76ee\u6807\u8868\u6240\u5728\u6570\u636e\u5e93\u800c\u8a00\u7684\u3002\u800cOracle\u53ef\u80fd\u6b63\u662f\u51fa\u4e8e\u7c7b\u4f3c\u7684\u8003\u8651\uff0c\u624d\u4f1a\u5c06\u8fdc\u7aef\u6570\u636e\u5e93\u548c\u672c\u5730\u6570\u636e\u5e93\u7684SCN\u8fdb\u884c\u540c\u6b65\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u76f4\u4e0d\u786e\u5b9a\u95ea\u56de\u67e5\u8be2\u7684AS OF\u8bed\u53e5\u662f\u5426\u53ef\u4ee5\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u5bf9\u8fdc\u7aef\u6570\u636e\u5e93\u751f\u6548\uff0c\u800c\u7531\u4e8e\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u67e5\u8be2\u4f1a\u4f7f\u4e24\u4e2a\u6570\u636e\u5e93\u7684SCN\u540c\u6b65\uff0c\u4f7f\u5f97\u8fd9\u4e2a\u95ee\u9898\u96be\u4ee5\u6d4b\u8bd5\u3002 \u901a\u8fc7\u6570\u636e\u5e93\u94fe\u67e5\u8be2\u4f1a\u5bfc\u81f4\u6e90\u548c\u76ee\u6807\u5e93SCN\u540c\u6b65\uff1ahttps:\/\/yangtingkun.net\/?p=722 \u867d\u7136Oracle\u4f1a\u5728\u901a\u8fc7\u6570\u636e\u5e93\u94fe\u6267\u884c\u67e5\u8be2\u6216DML\u64cd\u4f5c\u65f6\u540c\u6b65\u6570\u636e\u5e93\u7684SCN\uff0c\u4f46\u662f\u6307\u5b9a\u7684SCN\u662f\u672c\u5730\u7684\u8fd8\u662f\u8fdc\u7aef\u7684\u8fd8\u662f\u6709\u610f\u4e49\u7684\u3002 \u4e3a\u4e86\u8bf4\u660e\u8fd9\u79cd\u60c5\u51b5\uff0c\u521b\u5efa\u4e00\u4e2aJOB\uff0c\u5b9a\u671f\u8fdb\u884c\u6570\u636e\u7684\u4fee\u6539\uff0c\u4ee5\u5feb\u901f\u63d0\u9ad8\u4e00\u4e2a\u6570\u636e\u5e93\u7684SCN\uff1a SQL> select dbid, name from v$database; DBID NAME &#8212;&#8212;&#8212;- &#8212;&#8212;&#8212; 452011237 ORAT2 SQL> select count(*) from t; COUNT(*) &#8212;&#8212;&#8212;- 731791 SQL> begin 2 for i in 1..100000 loop 3 insert into t values (i, &#8216;y&#8217;); &hellip; <a href=\"https:\/\/yangtingkun.net\/?p=727\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3],"tags":[604,45],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2yiY3-bJ","_links":{"self":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/727"}],"collection":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=727"}],"version-history":[{"count":1,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/727\/revisions"}],"predecessor-version":[{"id":729,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=\/wp\/v2\/posts\/727\/revisions\/729"}],"wp:attachment":[{"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=727"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=727"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/yangtingkun.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=727"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}