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.

1. Enable the recycle bin.

   set global recyclebin = ON;
   

2. Drop the dws_ny table.

   obclient> drop table dws_ny;
   Query OK, 0 rows affected
   

3. Commit the transaction.

   obclient> commit;
   Query OK, 0 rows affected
   

4. View the recycle bin.

   obclient> SHOW RECYCLEBIN;
   +-------------------------------------+---------------+-------+------------------------------+
   | OBJECT_NAME                         | ORIGINAL_NAME | TYPE  | CREATETIME                   |
   +-------------------------------------+---------------+-------+------------------------------+
   | RECYCLE_$_20220421_1650614725195376 | DWS_NY        | TABLE | 22-APR-22 04.05.25.196009 PM |
   +-------------------------------------+---------------+-------+------------------------------+
   1 row in set
   

5. Flash back the dws_ny table from the recycle bin.

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

6. 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    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
     | NAME  | VARCHAR2(10) | YES  | NULL | NULL    | NULL  |
     +-------+--------------+------+-----+---------+-------+
     2 rows in set
     

   • Data in the dws_ny table:

     obclient> SELECT * FROM dws_ny;
     +------+--------+
     | ID   | NAME   |
     +------+--------+
     |    1 | Zhang San   |
     +------+--------+
     1 row in set