{"id":1418,"date":"2018-04-23T09:50:57","date_gmt":"2018-04-23T01:50:57","guid":{"rendered":"http:\/\/www.821121.com\/?p=1418"},"modified":"2021-02-23T09:54:08","modified_gmt":"2021-02-23T01:54:08","slug":"%e9%97%aa%e5%9b%9e%e8%a1%a8%ef%bc%88flashback-table%ef%bc%89%e6%a8%a1%e6%8b%9f%e6%b5%8b%e8%af%95","status":"publish","type":"post","link":"http:\/\/www.821121.com\/?p=1418","title":{"rendered":"\u95ea\u56de\u8868\uff08Flashback table\uff09\u6a21\u62df\u6d4b\u8bd5"},"content":{"rendered":"<pre><code class=\"language-sql\">Flashback table\u590d\u539f\u4e00\u4e2a\u8868\u5230\u67d0\u4e2a\u65f6\u95f4\u70b9\uff0c\u6216\u8005\u67d0\u4e2aSCN\u800c\u4e0d\u7528\u56de\u590d\u6570\u636e\u6587\u4ef6\u3002\u95ea\u56de\u8868\u4f9d\u8d56UNDO\u6570\u636e\uff0c\u5f53\u8868\u7ed3\u6784\u6539\u53d8\u7684\u65f6\u5019\uff0c\u4e0d\u80fd\u8fdb\u884c\u95ea\u56de\u3002Flashback drop\u7528\u6237\u6062\u590d\u88ab\u8bef\u5220\u9664\u7684\u8868\u3002\u5141\u8bb8\u4f60\u4ece\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u6062\u590d\u4e00\u4e2a\u88abdrop\u7684\u5bf9\u8c61\u3002\u5728\u6267\u884cdrop\u64cd\u4f5c\u65f6\uff0c\u73b0\u5728oracle\u4e0d\u662f\u771f\u6b63\u5220\u9664\u4ed6\uff0c\u800c\u662f\u5c06\u5bf9\u8c61\u81ea\u52a8\u653e\u5165\u56de\u6536\u7ad9\uff0c\u5bf9\u4e8e\u4e00\u4e2a\u5bf9\u8c61\u7684\u5220\u9664\uff0c\u5176\u5b9e\u5c31\u662f\u4e00\u4e2a\u7b80\u5355\u7684\u91cd\u547d\u4ee4\u64cd\u4f5c\uff0c\u5e76\u4e14\u6240\u5728\u7684\u8868\u7a7a\u95f4\u4e0d\u53d8\u3002\u8868\u4e0a\u9762\u7684\u7ea6\u675f\u4e5f\u5728\u653e\u5728\u56de\u6536\u7ad9\u91cc\u9762\uff0c\u5728\u95ea\u56de\u540e\uff0c\u7d22\u5f15\u7684\u540d\u79f0\u8fd8\u662f\u7cfb\u7edf\u9ed8\u8ba4\u7684\uff0c\u9700\u8981\u624b\u5de5\u8fd8\u539f\u3002\u8868\u4e0a\u7684\u7269\u5316\u89c6\u56fe\u65e5\u5fd7\u4e0d\u4f1a\u968f\u7740\u8868\u7684\u5220\u9664\u800c\u653e\u5165\u56de\u6536\u7ad9\u3002\n\u5bf9\u95ea\u56de\u8868\u8bed\u53e5\u4e0d\u80fd\u8fdb\u884c\u56de\u6eda\uff0c\u5982\u4f55\u8981\u95ea\u56de\u8868\uff0c\u4f60\u9700\u8981\u6709\u5bf9\u8868\u7684flashback\u5bf9\u8c61\u6743\u9650\u6216\u8005flashback any table\u7cfb\u7edf\u6743\u9650\u3002row movement\u5bf9\u5e94flash drop\u6ca1\u6709\u5f71\u54cd\uff0c\u4f46\u662f\u60f3\u8981\u4f7f\u7528\u95ea\u56de\u8868\u6765\u6062\u590d\u88ab\u5220\u9664\u7684\u6570\u636e\u65f6\uff0c\u5c31\u9700\u8981\u5f00\u542frow movement\u3002flashback drop\u4e0d\u4f1a\u6062\u590d\u8868\u76f8\u5173\u7684\u7ea6\u675f\u4fe1\u606f\nflashback table to scn\u6216\u8005to timestamp\uff0c\u5982\u679c\u5f53\u524d\u5b58\u5728\u7d22\u5f15\uff0c\u95ea\u56de\u5230\u521b\u5efa\u7d22\u5f15\u4e4b\u524d\u7684\u65f6\u95f4\u65f6\u5019\uff0c\u95ea\u56de\u540e\uff0c\u7cfb\u7edf\u4ecd\u7136\u5b58\u5728\u7d22\u5f15\u3002\u5982\u679c\u5f53\u524ddrop\u4e86\u7d22\u5f15\uff0c\u90a3\u95ea\u56de\u5230\u521b\u5efa\u7d22\u5f15\u7684\u65f6\u95f4\u70b9\u65f6\uff0c\u7d22\u5f15\u662f\u53d8\u6210\u6ca1\u6709\u4e86\u3002<\/code><\/pre>\n<pre><code class=\"language-sql\">`************************\u4e0b\u9762\u6d4b\u8bd5\u95ea\u56de\u8868*************************\nSQL&gt; conn coco\/coco;\nConnected.\nSQL&gt; create table t (a number,b number);\n\nTable created.\n\nSQL&gt; insert into t values(1,1);\n\n1 row created.\n\nSQL&gt; commit;\n\nCommit complete.\n\nSQL&gt; select dbms_flashback.get_system_change_number from dual;\n\nGET_SYSTEM_CHANGE_NUMBER\n------------------------\n                 2171977\n\nSQL&gt; insert into t values(2,2);\n\n1 row created.\n\nSQL&gt; commit;\n\nCommit complete.\n\nSQL&gt; select * from t;\n\n         A          B\n---------- ----------\n         1          1\n         2          2\n\nSQL&gt; select dbms_flashback.get_system_change_number from dual;\n\nGET_SYSTEM_CHANGE_NUMBER\n------------------------\n                 2172006\n\n----\u95ea\u56de\u8868\u7684\u65f6\u5019\uff0c\u9700\u8981\u5bf9\u8868\u6267\u884crow movment\n\nSQL&gt; alter table t enable row movement;\nTable altered.\nSQL&gt; flashback table t to scn 2171977;\nFlashback complete.\nSQL&gt; select * from t;------\u8868\u95ea\u56de\u5230\u5177\u4f53\u7684SCN\u65f6\u7684\u60c5\u5f62\n\nSQL&gt; select * from t;\n\n         A          B\n---------- ----------\n         1          1\n\nSQL&gt; flashback table t to scn 2172006;\nFlashback complete.\nSQL&gt; select * from t; ---\u95ea\u56de\u5230\u6700\u540e\u7684\u4f4d\u7f6e\n         A          B\n---------- ----------\n         1          1\n         2          2`<\/code><\/pre>\n<pre><code class=\"language-sql\">-------------------\u4e0b\u9762\u662f\u9a8c\u8bc1\u7d22\u5f15\u5728flashback table to scn\u4e2d\u7684\u60c5\u51b5---------------\nSQL&gt; drop table t;\nTable dropped.\nSQL&gt; create table t(a number,b number);\nTable created.\nSQL&gt; insert into t values(1,1);\n1 row created.\nSQL&gt; commit;\nCommit complete.\nSQL&gt; select dbms_flashback.get_system_change_number from dual;\nGET_SYSTEM_CHANGE_NUMBER\n------------------------\n                 2188977\nSQL&gt; insert into t values(2,2);\n1 row created.\nSQL&gt; commit;\nCommit complete.\nSQL&gt; create index idx_t on t(a);\nIndex created.\nSQL&gt; select dbms_flashback.get_system_change_number from dual; ---\u8be5\u65f6\u95f4\u70b9\u8868\u4e2d\u5b58\u5728\u7d22\u5f15\nGET_SYSTEM_CHANGE_NUMBER\n------------------------\n                 2189035\nSQL&gt; insert into t values(3,3);\n1 row created.\nSQL&gt; commit;\nCommit complete.\nSQL&gt; select dbms_flashback.get_system_change_number from dual;\nGET_SYSTEM_CHANGE_NUMBER\n------------------------\n                 2189068\n\nSQL&gt; select index_name,status from user_indexes where table_name=&#039;T&#039;;\nINDEX_NAME                     STATUS\n------------------------------ --------\nIDX_T                          VALID\n\nSQL&gt; alter table t enable row movement;\nTable altered.\nSQL&gt; flashback table t to scn 2188977;      ----\u6062\u590d\u5230\u6ca1\u6709\u521b\u5efa\u7d22\u5f15\u4e4b\u524d\u7684SCN\nFlashback complete.\nSQL&gt; select * from t;\n         A          B\n---------- ----------\n         1          1\nSQL&gt; select index_name,status from user_indexes where table_name=&#039;T&#039;;----\u6b64\u65f6\u7d22\u5f15\u4ecd\u7136\u5b58\u5728\nINDEX_NAME                     STATUS\n------------------------------ --------\nIDX_T                          VALID\n\nSQL&gt; flashback table t to scn 2189068;  ---\u95ea\u56de\u5230\u6700\u540e\u7684SCN\n\nFlashback complete.\nSQL&gt; select * from t;\n         A          B\n---------- ----------\n         1          1\n         2          2\n         3          3\n\nSQL&gt; drop index idx_t;----\u5220\u9664\u7d22\u5f15\nIndex dropped.\nSQL&gt; flashback table t to scn 2189035;-----\u95ea\u56de\u5230\u521b\u5efa\u7d22\u5f15\u7684SCN \uff0c\u4f46\u662f\u7ecf\u8fc7\u95ea\u56de\u540e\u7d22\u5f15\u5df2\u7ecf\u4e0d\u518d\u5b58\u5728\u4e86\nFlashback complete.\nSQL&gt; select index_name,status from user_indexes where table_name=&#039;T&#039;;\nno rows selected\nSQL&gt; <\/code><\/pre>\n<pre><code class=\"language-sql\">*****************\u89e6\u53d1\u5668************************\nENABLE | DISABLE TRIGGERS\n\u7f3a\u7701\u60c5\u51b5\u4e0b\uff0c\u95ea\u56de\u8868\u65f6\uff0c\u8868\u4e0a\u7684triggers\u662f\u4e0d\u80fd\u4f7f\u7528\u7684\uff0c\u53ef\u4ee5\u4f7f\u7528enable triggers\u6765\u662f\u95ea\u56de\u7684\u540c\u65f6\u542f\u7528triggers\n\nTO BEFORE DROP Clause\nUsing Flashback Drop and Managing the Recycle Bin\nWhen you drop a table, the database does not immediately remove the space associated with the table. The database renames the table and places it and any associated objects in a recycle bin, where, in case the table was dropped in error, it can be recovered at a later time. This feature is called Flashback Drop, and the FLASHBACK TABLE statement is used to restore the table. Before discussing the use of the FLASHBACK TABLE statement for this purpose, it is important to understand how the recycle bin works, and how you manage its contents.\n\n---\u5f53\u4f60\u5220\u9664\u4e00\u5f20\u8868\uff0c\u6570\u636e\u5e93\u4e0d\u662f\u7acb\u523b\u5220\u9664\u8868\u6240\u5360\u7684\u7a7a\u95f4\uff0c\u6570\u636e\u5e93\u4f1a\u91cd\u547d\u540d\u8868\u5e76\u628a\u5b83\u548c\u76f8\u5173\u7684\u5bf9\u8c61\u653e\u5728\u56de\u6536\u7ad9\u91cc\u9762\uff0c\u9632\u6b62\u8868\u88ab\u8bef\u5220\u9664\u4e4b\u540e\uff0c\u80fd\u591f\u8fdb\u884c\u6062\u590d\uff0c\u8fd9\u4e2a\u7279\u5f81\u5c31\u53eb\u505a\u95ea\u56de\u5220\u9664\uff0c\u4f7f\u7528\nflashbackup table\u7ee7\u7eed\u8868\u7684\u6062\u590d\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Flashback table\u590d\u539f\u4e00\u4e2a\u8868\u5230\u67d0\u4e2a\u65f6\u95f4<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,40],"tags":[],"class_list":["post-1418","post","type-post","status-publish","format-standard","hentry","category-oracle-11g","category--oracle-11g"],"_links":{"self":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1418"}],"version-history":[{"count":1,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions"}],"predecessor-version":[{"id":1419,"href":"http:\/\/www.821121.com\/index.php?rest_route=\/wp\/v2\/posts\/1418\/revisions\/1419"}],"wp:attachment":[{"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1418"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.821121.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}