FLASHBACK

Purpose

This statement is used to restore deleted database objects, including tenants, databases, and tables, from the recycle bin.

By default, the recycle bin is enabled, and this behavior is controlled by the system variable recyclebin. You can use the SHOW VARIABLES LIKE 'recyclebin' statement to check whether the recycle bin is enabled. For more information about the system variable recyclebin, see the System Variables section in the Reference Guide.

obclient> SHOW VARIABLES LIKE 'recyclebin';

The query result is as follows:

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

If the recycle bin is disabled, you can enable it by using the SET recyclebin = on statement. Database objects in the recycle bin are not actually deleted and still occupy resources. If you want to permanently delete them, you can execute the PURGE recyclebin statement to clear the recycle bin.

When a table is deleted, its dependent indexes are also deleted. When you use the FLASHBACK statement to restore the table, the indexes on the table are also restored. Specifically, indexes directly deleted from the table do not enter the recycle bin, and OceanBase Database does not support directly purging indexes from the recycle bin.

Syntax

 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

Parameters

Examples

• Restore the deleted tenant tenant1 from the recycle bin.

  obclient> FLASHBACK TENANT tenant1 TO BEFORE DROP;
  

• Restore the deleted database from the recycle bin.

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

  The query result is as follows:

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

  Restore the database from the recycle bin:

  obclient> FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;
  

• Restore the deleted table product_info from the recycle bin and rename it to product_backup. Then, check the restoration status of the indexes.

  Create a table and add indexes:

  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;
  

  The query result is as follows:

  +--------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | 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
  

  Delete the indexes and check the recycle bin:

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

  The query result is as follows:

  Empty set
  

  Delete the table and check the recycle bin:

  obclient> DROP TABLE product_info;
  obclient> SHOW RECYCLEBIN;
  

  The query result is as follows:

  +---------------------------------+---------------------------+-------+----------------------------+
  | 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
  

  Restore the table from the recycle bin and rename it:

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

  The query result is as follows:

  +----------------+------------+-----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | 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