FLASHBACK
Description
This statement is used to restore deleted database objects, including 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 check whether the recycle bin is enabled by executing the SHOW VARIABLES LIKE 'recyclebin' statement. For more information about the recyclebin system variable, see recyclebin.
SHOW VARIABLES LIKE 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.001 sec)
If the recycle bin is disabled, you can enable it by executing 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 clean up the recycle bin.
When a table is deleted, all its dependent indexes are also deleted. When you restore the table using the FLASHBACK statement, the indexes on the table are also restored. Specifically, indexes directly deleted from the table do not go into the recycle bin, and seekdb does not support directly recovering indexes from the recycle bin.
Syntax
FLASHBACK {DATABASE | TABLE} object_name TO BEFORE DROP [RENAME TO flashback_object_name];
flashback_object_name:
| database_name
| database_name.table_name
Parameter Description
| Parameter | Description |
|---|---|
| object_name | Specifies the name of the database object in the recycle bin to be restored. You cannot directly specify the name. When you restore a database object, all dependent objects such as tables and indexes in the database are also restored. You can use the SHOW RECYCLEBIN statement to view the name of the database object in the recycle bin that needs to be restored. |
| RENAME to | Specifies the new name for the database object during restoration. |
| database_name | The new name of the database after restoration. |
| database_name.table_name | The new name of the table after restoration, where database_name specifies the database to which the table belongs after restoration. |
Examples
-
Restore a deleted database from the recycle bin.
CREATE DATABASE da;
Query OK, 1 row affected (0.053 sec)
DROP DATABASE da;
Query OK, 0 rows affected (0.051 sec)
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 (0.001 sec)
FLASHBACK DATABASE __recycle_$_1_1099511628829_18446744073709551615 TO BEFORE DROP;
Query OK, 0 rows affected (0.043 sec) -
Restore the deleted table
t1from the recycle bin and rename it tot2. Then, check the restoration of indexes.CREATE TABLE t1(c1 INT PRIMARY KEY,c2 INT,c3 INT);
Query OK, 0 rows affected (0.073 sec)
ALTER TABLE t1 ADD INDEX ind2 (c2) USING BTREE;
Query OK, 0 rows affected (0.440 sec)
ALTER TABLE t1 ADD INDEX ind3 (c3) USING BTREE;
Query OK, 0 rows affected (0.427 sec)
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 (0.006 sec)
ALTER TABLE t1 DROP INDEX ind2;
Query OK, 0 rows affected (0.231 sec)
SHOW RECYCLEBIN;
Empty set (0.006 sec)
DROP TABLE t1;
Query OK, 0 rows affected (0.107 sec)
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 (0.002 sec)
FLASHBACK TABLE __recycle_$_10_1628157070067712 TO BEFORE DROP RENAME TO t2;
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 (0.048 sec)