[TOC] # FLASHBACK TABLE ## 描述 用于從回收站中恢復被刪除的TABLE。 ## 前置條件 回收站需要處于開啟狀態，可以通過 `show variables like 'recyclebin';` 來查看回收站是否開啟。 ~~~ OceanBase(admin@test)> show variables like 'recyclebin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | recyclebin | ON | +---------------+-------+ 1 row in set (0.00 sec) ~~~ 如果回收站處于關閉狀態，可以通過 `set recyclebin = on;` 來開啟。回收站中的表沒有被實際刪除，仍然會占用資源，如果需要徹底刪除，可以執行 `purge recyclebin;` ## 格式 ~~~ FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name]; ~~~ ## 參數解釋 <table data-tag="table" id="table-e6a-40l-075" class="table"><colgroup width="360" span="1" data-tag="col" id="col-qtc-aes-5p7" colwidth="1*" colnum="1" colname="col1" style="width:50%" class="col"></colgroup><colgroup width="360" span="1" data-tag="col" id="col-r3j-3bb-ufo" colwidth="1*" colnum="2" colname="col2" style="width:50%" class="col"></colgroup><thead id="thead-2e1-rr3-07r" class="thead"><tr id="tr-3jm-gr2-5rx"><th id="td-gim-m8m-3j9"><p id="p-vhi-ew4-6td"><b>參數</b></p></th><th id="td-1co-4l8-aon"><p id="p-any-ns7-pt3"><b>解釋</b></p></th></tr></thead><tbody data-tag="tbody" id="tbody-sar-4a3-q6l" class="tbody"><tr data-tag="tr" id="tr-ztb-r96-58g" class="tr"><td data-tag="td" id="td-ye9-cf9-54y" class="td"><p id="p-tk5-tkj-ob5">object_name</p></td><td data-tag="td" id="td-vik-4w2-7uw" class="td"><p data-tag="p" id="p-vyd-lkq-v38" class="p">指定要恢復的object名稱或表名，只有在表所在的database中才能執行。恢復TABLE時，也會將和TABLE相關的INDEX也恢復出來。</p></td></tr><tr data-tag="tr" id="tr-m04-8xs-67y" class="tr"><td data-tag="td" id="td-m6m-cr2-mgy" class="td"><p id="p-wso-0dj-vfx">RENAME to</p></td><td data-tag="td" id="td-qva-j0z-xbq" class="td"><p data-tag="p" id="p-m0k-o61-bus" class="p">修改表名和表所屬的庫。</p></td></tr></tbody></table> ## 示例 * 從回收站中恢復被刪除的表t。 ~~~ OceanBase(admin@test)> create table t(id int primary key, k int); Query OK, 0 rows affected (0.04 sec) OceanBase(admin@test)> insert into t values(1,1); Query OK, 1 row affected (0.00 sec) OceanBase(admin@test)> select * from t; +----+------+ | id | k | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec) OceanBase(admin@test)> drop table t; Query OK, 0 rows affected (0.01 sec) OceanBase(admin@test)> select * from t; ERROR 1146 (42S02): Table 'test.t' does not exist OceanBase(admin@test)> show recyclebin; +--------------------------------+---------------+-------+----------------------------+ | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME | +--------------------------------+---------------+-------+----------------------------+ | __recycle_$_1_1597028971700936 | t | TABLE | 2020-08-10 11:09:31.701033 | +--------------------------------+---------------+-------+----------------------------+ 1 row in set (0.00 sec) OceanBase(admin@test)> flashback table t to before drop; Query OK, 0 rows affected (0.01 sec) OceanBase(admin@test)> select * from t; +----+------+ | id | k | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec) ~~~