Purpose
This statement is used to restore deleted database objects from the recycle bin, including tenants, databases, tables, etc.
By default, the recycle bin is enabled. This behavior is controlled by the system variable recyclebin. You can run 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 running the SET recyclebin = on statement. Database objects in the recycle bin are not actually deleted and still occupy resources. To permanently delete them, you can execute the PUREGE recyclebin statement to clean up the recycle bin.
When a table is dropped, its indexes are also dropped. When you restore a table using the FLASHBACK statement, the indexes on the table are restored as well. Specifically, indexes dropped directly from a table do not go to the recycle bin, and OceanBase AI Database does not support directly recovering 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
Parameters |
Explanation |
|---|---|
| object_name | Specify the name of the database object to be restored in the recycle bin. Direct name specification is not supported. When restoring a database object, its dependent objects such as tables and indexes are also restored. You can use the SHOW RECYCLEBIN statement to view the name of the database to be restored in the recycle bin. |
| RENAME to | Rename database objects during restore. |
| tenant_name | The name of the tenant after recovery. |
| database_name | Name after database recovery |
| database_name.table_name | The name of the restored table, where database_name Specifies the database to which the restored table will become subordinate. |
Examples
Restore the deleted tenant
tenant1from 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 setRestore the database from the recycle bin:
obclient> FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;Restore the deleted table
product_infofrom the recycle bin, rename it toproduct_backup, and check the index restoration.Create a table and add an index:
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 setDelete an index and view the recycle bin:
obclient> ALTER TABLE product_info DROP INDEX idx_name; obclient> SHOW RECYCLEBIN;The query result is as follows:
Empty setDelete a table and view 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 setRestore 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