描述

用于从回收站中恢复被删除的TABLE。

前置条件

回收站需要处于开启状态，可以通过

show variables like 'recyclebin';

来查看回收站是否开启。

  1. OceanBase(admin@test)> show variables like 'recyclebin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | recyclebin    | ON    |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)

如果回收站处于关闭状态，可以通过

set recyclebin = on;

来开启。回收站中的表没有被实际删除，仍然会占用资源，如果需要彻底删除，可以执行

purge recyclebin;

格式

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

参数解释

参数

解释

object_name

指定要恢复的object名称或表名，只有在表所在的database中才能执行。恢复TABLE时，也会将和TABLE相关的INDEX也恢复出来。

RENAME to

修改表名和表所属的库。

示例

  • 从回收站中恢复被删除的表t。
  1. OceanBase(admin@test)> create table t(id int primary key, k int);
  2. Query OK, 0 rows affected (0.04 sec)
  4. OceanBase(admin@test)> insert into t values(1,1);
  5. Query OK, 1 row affected (0.00 sec)
  7. OceanBase(admin@test)> select * from t;
  8. +----+------+
  9. | id | k    |
  10. +----+------+
  11. |  1 |    1 |
  12. +----+------+
  13. 1 row in set (0.00 sec)
  15. OceanBase(admin@test)> drop table t;
  16. Query OK, 0 rows affected (0.01 sec)
  18. OceanBase(admin@test)> select * from t;
  19. ERROR 1146 (42S02): Table 'test.t' does not exist
  20. OceanBase(admin@test)> show recyclebin;
  21. +--------------------------------+---------------+-------+----------------------------+
  22. | OBJECT_NAME                    | ORIGINAL_NAME | TYPE  | CREATETIME                 |
  23. +--------------------------------+---------------+-------+----------------------------+
  24. | __recycle_$_1_1597028971700936 | t             | TABLE | 2020-08-10 11:09:31.701033 |
  25. +--------------------------------+---------------+-------+----------------------------+
  26. 1 row in set (0.00 sec)
  28. OceanBase(admin@test)> flashback table t to before drop;
  29. Query OK, 0 rows affected (0.01 sec)
  31. OceanBase(admin@test)> select * from t;
  32. +----+------+
  33. | id | k    |
  34. +----+------+
  35. |  1 |    1 |
  36. +----+------+
  37. 1 row in set (0.00 sec)