Purpose
You can use this statement to restore a dropped table from the recycle bin.
Note
- Tables in the recycle bin are not actually deleted and still occupy resources. To permanently delete the contents of the recycle bin, execute the SQL statement
PURGE RECYCLEBIN;. - A dropped table is 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 [schema.]table_name [, [schema.]table_name]...
TO BEFORE DROP
[RENAME TO [schema.]new_table_name]
Parameters
| Parameter | Description |
|---|---|
| table_name | the name of the table to be restored. Note When you restore a table, the indexes associated with the table are also restored. |
| RENAME TO | the new name and schema of the table. If you omit RENAME TO database_name.table_name, the restored table retains its original name. |
Examples
Restore the dropped table
tbl1from the recycle bin.View the contents of the recycle bin.
obclient> SHOW RECYCLEBIN;Return result
+-----------------------------------+---------------+-------+------------------------------+ | 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)Execute the restore operation.
obclient> FLASHBACK TABLE tbl1 TO BEFORE DROP;Verify the restore result.
obclient> SELECT * FROM tbl1;Return result
+------+------+ | COL1 | COL2 | +------+------+ | 1 | 1 | +------+------+ 1 row in setRestore the dropped table
tbl2to theuser1schema and rename it totbl_test.View the contents of the recycle bin.
obclient> SHOW RECYCLEBIN;Return result
+-----------------------------------+---------------+-------+------------------------------+ | 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)Execute the restore and rename operation.
obclient> FLASHBACK TABLE tbl2 TO BEFORE DROP RENAME TO user1.tbl_test;Verify the restore result.
obclient> SELECT * FROM user1.tbl_test;Return result
+------+------+ | COL1 | COL2 | +------+------+ | 1 | 1 | +------+------+ 1 row in set
Considerations
To use the FLASHBACK TABLE statement, you must enable the recycle bin. For more information, see Recycle bin.