OceanBase has a recycle bin that allows you to flash back deleted tables. The recycle bin is enabled by default. You can enable and disable the recycle bin by setting the recyclebin parameter.
Syntax for enabling or disabling the recycle bin:
set global recyclebin = ON | OFF ;
The change in the recycle bin status takes effect in new sessions.
- Example: Flashing back a deleted table
obclient> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)
obclient> create table t1(id bigint not null primary key, gmt_create datetime not null default current_timestamp);
Query OK, 0 rows affected (0.09 sec)
obclient> insert into t1(id) values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
obclient> select * from t1;
+----+---------------------+
| ID | GMT_CREATE |
+----+---------------------+
| 1 | 2020-02-28 09:47:07 |
| 2 | 2020-02-28 09:47:07 |
| 3 | 2020-02-28 09:47:07 |
+----+---------------------+
3 rows in set (0.00 sec)
obclient> drop table t1;
Query OK, 0 rows affected (0.03 sec)
obclient> show recyclebin\G
*************************** 1. row ***************************
OBJECT_NAME: __recycle_$_20200102_1585650066255592
ORIGINAL_NAME: t1
TYPE: TABLE
CREATETIME: 2020-03-31 18:21:06.255716
1 row in set (0.03 sec)
obclient> flashback table __recycle_$_20200102_1585650066255592 to before drop rename to t1;
Query OK, 0 rows affected (0.02 sec)
obclient> select * from t1;
+----+---------------------+
| ID | GMT_CREATE |
+----+---------------------+
| 1 | 2020-02-28 09:47:07 |
| 2 | 2020-02-28 09:47:07 |
| 3 | 2020-02-28 09:47:07 |
+----+---------------------+