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> create table t1(id number not null primary key, gmt_create date not null default sysdate);
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-04-02 18:20:49 |
| 2 | 2020-04-02 18:20:49 |
| 3 | 2020-04-02 18:20:49 |
+----+---------------------+
3 rows in set (0.00 sec)
obclient> drop table t1;
Query OK, 0 rows affected (0.03 sec)
obclient> show recyclebin;
+-------------------------------------+------------------------------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+-------------------------------------+------------------------------------+-------+----------------------------+
| RECYCLE_$_20200102_1585822884952336 | T1 | TABLE | 2020-04-02 18:21:24.952475 |
+-------------------------------------+------------------------------------+-------+----------------------------+
1 rows in set (0.01 sec)
obclient> flashback table RECYCLE_$_20200102_1585822884952336 to before drop rename to t1;
Query OK, 0 rows affected (0.02 sec)
obclient> select * from t1;
+----+---------------------+
| ID | GMT_CREATE |
+----+---------------------+
| 1 | 2020-04-02 18:20:49 |
| 2 | 2020-04-02 18:20:49 |
| 3 | 2020-04-02 18:20:49 |
+----+---------------------+
3 rows in set (0.00 sec)