All Products
Search
Document Center

FLASHBACK TABLE

Last Updated: Jun 18, 2021

Description

You can execute the FLASHBACK TABLE statement to restore a deleted table from the recycle bin.

Prerequisites

The recycle bin is enabled. You can execute the following statement to check whether the recycle bin is enabled:

show variables like 'recyclebin';

OceanBase(admin@test)> show variables like 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

If the recycle bin is disabled, you can execute the following statement to enable it:

set recyclebin = on;

Tables in the recycle bin are not deleted from the database and still consume resources. To delete them from the database, execute the following statement:

purge recyclebin;

Syntax

 FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name];

Parameters

Parameter

Description

object_name

The name of the object or table that you want to restore. Before you execute FLASHBACK TABLE to restore a table, log on to the database where the table is stored. When you restore a table, the index of the table is also restored.

RENAME to

Rename the table and database to which the table belongs.

Examples

  • Restore deleted table t from the recycle bin.
OceanBase(admin@test)> create table t(id int primary key, k int);
Query OK, 0 rows affected (0.04 sec)

OceanBase(admin@test)> insert into t values(1,1);
Query OK, 1 row affected (0.00 sec)

OceanBase(admin@test)> select * from t;
+----+------+
| id | k    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)

OceanBase(admin@test)> drop table t;
Query OK, 0 rows affected (0.01 sec)

OceanBase(admin@test)> select * from t;
ERROR 1146 (42S02): Table 'test.t' does not exist
OceanBase(admin@test)> show recyclebin;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME                    | ORIGINAL_NAME | TYPE  | CREATETIME                 |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1597028971700936 | t             | TABLE | 2020-08-10 11:09:31.701033 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.00 sec)

OceanBase(admin@test)> flashback table t to before drop;
Query OK, 0 rows affected (0.01 sec)

OceanBase(admin@test)> select * from t;
+----+------+
| id | k    |
+----+------+
|  1 |    1 |
+----+------+
1 row in set (0.00 sec)