FLASHBACK

Purpose

You can use this statement to restore database objects, such as tenants, databases, and tables, from the recycle bin.

The recycle bin is enabled by default. The recyclebin parameter specifies whether to enable the recycle bin. You can use the SHOW VARIABLES LIKE 'recyclebin' statement to check whether the recycle bin is enabled. For more information about the recyclebin parameter, see System variables in the Reference Guide.

obclient> SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set

If the recycle bin is disabled, you can execute the SET recyclebin = on statement to enable it. Database objects in the recycle bin are not physically deleted and still occupy resources. You can execute the PURGE recyclebin statement to purge them from the recycle bin.

After a table is dropped, indexes on the table are also dropped. When you execute the FLASHBACK statement to restore the table, indexes on the table are also restored. Indexes that are directly dropped from tables are not placed in the recycle bin. In addition, OceanBase Database does not allow you to directly recover 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 tenant tenant1 from the recycle bin.

  obclient> FLASHBACK TENANT tenant1 TO BEFORE DROP;
  Query OK, 0 rows affected
  

• Restore a database from the recycle bin.

  obclient> CREATE DATABASE da;
  Query OK, 1 row affected
  
  obclient> DROP DATABASE da;
  Query OK, 0 rows affected
  
  obclient> SHOW RECYCLEBIN;
  +--------------------------------------------------+---------------+----------+----------------------------+
  | OBJECT_NAME                                      | ORIGINAL_NAME | TYPE     | CREATETIME                 |
  +--------------------------------------------------+---------------+----------+----------------------------+
  | __recycle_$_1_1099511628829_18446744073709551615 | da            | DATABASE | 2017-10-20 17:36:15.838771 |
  +--------------------------------------------------+---------------+----------+----------------------------+
  1 row in set
  
  obclient> FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;
  Query OK, 0 rows affected
  

• Restore table t1 from the recycle bin, rename it as t2, and query the restoration status of indexes.

  obclient> CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT,c3 INT);
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t1 ADD INDEX ind2 (c2) USING BTREE;
  Query OK, 0 rows affected
  
  obclient> ALTER TABLE t1 ADD INDEX ind3 (c3) USING BTREE;
  Query OK, 0 rows affected
  
  obclient> SHOW INDEX FROM t1;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | t1    |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | t1    |          1 | ind2     |            1 | c2          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  | t1    |          1 | ind3     |            1 | c3          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  3 rows in set
  
  obclient> ALTER TABLE t1 DROP INDEX ind2;
  Query OK, 0 rows affected
  
  obclient> SHOW RECYCLEBIN;
  Empty set
  
  obclient> DROP TABLE t1;
  Query OK, 0 rows affected
  
  obclient> SHOW RECYCLEBIN;
  +---------------------------------+--------------------------+-------+----------------------------+
  | OBJECT_NAME                     | ORIGINAL_NAME            | TYPE  | CREATETIME                 |
  +---------------------------------+--------------------------+-------+----------------------------+
  | __recycle_$_10_1628157070059520 | __idx_1099511677777_ind3 | INDEX | 2021-08-05 17:51:10.060761 |
  | __recycle_$_10_1628157070067712 | t1                       | TABLE | 2021-08-05 17:51:10.068062 |
  +---------------------------------+--------------------------+-------+----------------------------+
  2 rows in set
  
  obclient> FLASHBACK TABLE __recycle_$_10_1628157070067712 TO BEFORE DROP RENAME TO t2;
  
  obclient> SHOW INDEX FROM t2;
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment   | Index_comment | Visible |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  | t2    |          0 | PRIMARY  |            1 | c1          | A         |        NULL | NULL     | NULL   |      | BTREE      | available |               | YES     |
  | t2    |          1 | ind3     |            1 | c3          | A         |        NULL | NULL     | NULL   | YES  | BTREE      | available |               | YES     |
  +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+
  2 rows in set