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.
- You can execute the SQL statement
Syntax
FLASHBACK TABLE original_name TO BEFORE DROP [RENAME TO database_name.table_name];
Parameters
| Parameter | Description |
|---|---|
| original_name | The name of the table to be restored. Note: When you restore a table, the indexes on the table are also restored. |
| RENAME TO | Renames the table and the database to which the table belongs. If RENAME TO database_name.table_name is omitted, the table is restored with its original name. |
Examples
Restore the dropped table
tbl1from 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 setRestore the dropped table
tbl2from the recycle bin to theuser1database, and rename the restored table astbl_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
