OceanBase FLASHBACK TABLE

描述

用于從回收站中恢復(fù)被刪除的 TABLE。

前置條件

回收站需要處于開(kāi)啟狀態(tài)，可以通過(guò)

?show variables like 'recyclebin';?

來(lái)查看回收站是否開(kāi)啟。

OceanBase(admin@test)> show variables like 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

如果回收站處于關(guān)閉狀態(tài)，可以通過(guò)

?set recyclebin = on;?

來(lái)開(kāi)啟?；厥照局械谋頉](méi)有被實(shí)際刪除，仍然會(huì)占用資源，如果需要徹底刪除，可以執(zhí)行

?purge recyclebin;?

格式

 FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name];

參數(shù)解釋

示例

• 從回收站中恢復(fù)被刪除的表 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)