Flash back a dropped table

OceanBase Database has a recycle bin that allows you to flash back dropped tables. The recycle bin is enabled by default. You can enable and disable the recycle bin by setting the tenant variable Recyclebin.

Prerequisites

The current user has the user-level SUPER privilege.

Enable or disable the recycle bin

Syntax

SET GLOBAL recyclebin = ON | OFF;

Note

The change in the recycle bin status takes effect in new sessions.

Examples

Flash back the dws_ny table from the recycle bin.

• Enable the recycle bin.

  SET GLOBAL recyclebin = ON;
  

• Drop the dws_ny table.

  obclient> DROP TABLE dws_ny;
  Query OK, 0 rows affected
  

• View the recycle bin.

  obclient> SHOW recyclebin\G
  *************************** 1. row ***************************
    OBJECT_NAME: __recycle_$_100051_1647945836520208
  ORIGINAL_NAME: dws_ny
           TYPE: TABLE
     CREATETIME: 2022-03-22 18:43:56.521081
  1 row in set
  

• Flash back the dws_ny table from the recycle bin.

  obclient> FLASHBACK TABLE __recycle_$_100051_1647945836520208 TO BEFORE DROP rename TO dws_ny;
  

• View the dws_ny table flashed back from the recycle bin.

  • Structure of the dws_ny table:

    obclient> DESC dws_ny;
    +-------+--------------+------+-----+---------+-------+
    | Field | Type         | Null | Key | Default | Extra |
    +-------+--------------+------+-----+---------+-------+
    | id    | int(11)      | NO   | PRI | NULL    |       |
    | name  | varchar(200) | YES  |     | NULL    |       |
    | date  | date         | YES  |     | NULL    |       |
    +-------+--------------+------+-----+---------+-------+
    3 rows in set
    

  • Data in the dws_ny table:

    obclient> SELECT * FROM dws_ny;
    +----+--------+------------+
    | id | name   | date       |
    +----+--------+------------+
    |  1 | Zhang San   | 0000-00-00 |
    |  2 | Li Si   | 0000-00-00 |
    +----+--------+------------+
    2 rows in set