FLASHBACK TABLE BEFORE DROP

Purpose

You can use this statement to restore a dropped table from the recycle bin.

Note

• The tables in the recycle bin are not actually dropped and still occupy resources. To permanently drop the tables from the recycle bin, execute the SQL statement PURGE RECYCLEBIN;.
• Dropped tables will be moved to the recycle bin only if the recycle bin is enabled.
  • You can execute the SQL statement SHOW VARIABLES LIKE 'recyclebin'; to check whether the recycle bin is enabled.
  • You can execute the SQL statement SET RECYCLEBIN = ON; to enable the recycle bin.

Syntax

 FLASHBACK TABLE original_name TO BEFORE DROP [RENAME TO database_name.table_name];

Parameters

Examples

• Restore the dropped table tbl1 from the recycle bin.

  obclient> SHOW RECYCLEBIN;
  +-----------------------------------+---------------+-------+------------------------------+
   OBJECT_NAME                        ORIGINAL_NAME  TYPE   CREATETIME                   
  +-----------------------------------+---------------+-------+------------------------------+
   RECYCLE_$_100004_1634807168766392  TBL1           TABLE  21-OCT-21 05.06.08.767109 PM 
   RECYCLE_$_100004_1634807177792816  TBL2           TABLE  21-OCT-21 05.06.17.791967 PM 
  +-----------------------------------+---------------+-------+------------------------------+
  2 rows in set (0.00 sec)
  
  obclient> FLASHBACK TABLE tbl1 TO BEFORE DROP;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM tbl1;
  +------+------+
   COL1  COL2 
  +------+------+
      1     1 
  +------+------+
  1 row in set
  

• Restore the dropped table tbl2 from the recycle bin to the user1 database, and rename the restored table as tbl_test.

  obclient> SHOW RECYCLEBIN;
  +-----------------------------------+---------------+-------+------------------------------+
   OBJECT_NAME                        ORIGINAL_NAME  TYPE   CREATETIME                   
  +-----------------------------------+---------------+-------+------------------------------+
   RECYCLE_$_100004_1634807168766392  TBL1           TABLE  21-OCT-21 05.06.08.767109 PM 
   RECYCLE_$_100004_1634807177792816  TBL2           TABLE  21-OCT-21 05.06.17.791967 PM 
  +-----------------------------------+---------------+-------+------------------------------+
  2 rows in set (0.00 sec)
  
  obclient> FLASHBACK TABLE tbl2 TO BEFORE DROP RENAME TO user1.tbl_test;
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM user1.tbl_test;
  +------+------+
   COL1  COL2 
  +------+------+
      1     1 
  +------+------+
  1 row in set