FLASHBACK

描述

该语句用于从回收站中恢复被删除的数据库对象，包括租户、数据库、表等。

默认情况下，回收站处于开启状态，该行为由系统变量 recyclebin 控制。您可以通过 SHOW VARIABLES LIKE 'recyclebin' 查看回收站是否开启。有关系统变量 recyclebin 的详细信息，请参见《参考指南》中的 系统变量 章节。

obclient> SHOW VARIABLES LIKE 'recyclebin';

查询结果如下：

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set

如果回收站处于关闭状态，可以通过 SET recyclebin = on 语句开启回收站。回收站中的数据库对象没有被实际删除，仍然会占用资源，如果需要彻底删除，可以执行 PUREGE recyclebin 语句清理回收站。

在删除表后，会同时也会删除从属该表的索引，在通过 FLASHBACK 语句恢复表时，表上的索引也会恢复。特别地，从表中直接删除的索引不会进入回收站，OceanBase 数据库也不支持从回收站中直接回收索引。

语法

 FLASHBACK {TENANT | DATABASE | TABLE} object_name TO BEFORE DROP [RENAME TO flashback_object_name]

flashback_object_name:
    tenant_name
  | database_name
  | database_name.table_name

参数解释

示例

• 从回收站中恢复被删除的租户 tenant1。

  obclient> FLASHBACK TENANT tenant1 TO BEFORE DROP;
  

• 从回收站中恢复删除的数据库。

  obclient> CREATE DATABASE sales_db;
  obclient> DROP DATABASE sales_db;
  obclient> SHOW RECYCLEBIN;
  

  查询结果如下：

  +--------------------------------------------------+---------------+----------+----------------------------+
  | OBJECT_NAME                                      | ORIGINAL_NAME | TYPE     | CREATETIME                 |
  +--------------------------------------------------+---------------+----------+----------------------------+
  | __recycle_$_1_1099511628829_18446744073709551615 | sales_db      | DATABASE | 2017-10-20 17:36:15.838771 |
  +--------------------------------------------------+---------------+----------+----------------------------+
  1 row in set
  

  从回收站中恢复数据库：

  obclient> FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;
  

• 从回收站中恢复被删除的表 product_info，重命名为 product_backup，并查看索引的恢复情况。

  创建表并添加索引：

  obclient> CREATE TABLE product_info(product_id INT PRIMARY KEY, product_name VARCHAR(50), price DECIMAL(10,2));
  obclient> ALTER TABLE product_info ADD INDEX idx_name (product_name) USING BTREE;
  obclient> ALTER TABLE product_info ADD INDEX idx_price (price) USING BTREE;
  obclient> SHOW INDEX FROM product_info;
  

  查询结果如下：

  +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table        | Non_unique | Key_name | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | product_info |          0 | PRIMARY  |            1 | product_id   | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | product_info |          1 | idx_name |            1 | product_name | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  | product_info |          1 | idx_price|            1 | price        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  3 rows in set
  

  删除索引并查看回收站：

  obclient> ALTER TABLE product_info DROP INDEX idx_name;
  obclient> SHOW RECYCLEBIN;
  

  查询结果如下：

  Empty set
  

  删除表并查看回收站：

  obclient> DROP TABLE product_info;
  obclient> SHOW RECYCLEBIN;
  

  查询结果如下：

  +---------------------------------+---------------------------+-------+----------------------------+
  | OBJECT_NAME                     | ORIGINAL_NAME             | TYPE  | CREATETIME                 |
  +---------------------------------+---------------------------+-------+----------------------------+
  | __recycle_$_10_1628157070059520 | __idx_1099511677777_idx_price | INDEX | 2021-08-05 17:51:10.060761 |
  | __recycle_$_10_1628157070067712 | product_info              | TABLE | 2021-08-05 17:51:10.068062 |
  +---------------------------------+---------------------------+-------+----------------------------+
  2 rows in set
  

  从回收站中恢复表并重命名：

  obclient> FLASHBACK TABLE __recycle_$_10_1628157070067712 TO BEFORE DROP RENAME TO product_backup;
  obclient> SHOW INDEX FROM product_backup;
  

  查询结果如下：

  +----------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table          | Non_unique | Key_name  | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +----------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | product_backup |          0 | PRIMARY   |            1 | product_id   | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | product_backup |          1 | idx_price |            1 | price        | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +----------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  2 rows in set