FLASHBACK TABLE · k8s原理与实践

[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) ~~~