Description
This statement restores the deleted tables from the recycle bin.
Prerequisites
The recycle bin is enabled. You can execute show variables like 'recyclebin'; to check whether the recycle bin is enabled.
obclient> show variables like 'recyclebin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| recyclebin | ON |
+---------------+-------+
1 row in set (0.00 sec)
If the recycle bin feature is in the disabled state, you can execute set recyclebin = on; to enable the recycle bin. The tables in the recycle bin are not actually deleted and still occupy resources. To completely delete these tables, execute purge recyclebin;.
Syntax
FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name];
Parameter description
| Parameter | Description |
|---|---|
| object_name | Specifies the name of the object or the table to be restored. The object or the table can be restored in only the database where the table resides. When you restore a table, the indexes that are related to the table are also restored. |
| RENAME to | Modifies the table name and the database to which the table belongs. |
Examples
- Restore the deleted table t from the recycle bin.
obclient> create table t(id int primary key, k int);
Query OK, 0 rows affected (0.04 sec)
obclient> insert into t values(1,1);
Query OK, 1 row affected (0.00 sec)
obclient> select * from t;
+----+------+
| id | k |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
obclient>> drop table t;
Query OK, 0 rows affected (0.01 sec)
obclient> select * from t;
ORA-00942: table or view 'SYS.T' does not exist
obclient> show recyclebin;
+--------------------------------+---------------+-------+----------------------------+
| OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
+--------------------------------+---------------+-------+----------------------------+
| __recycle_$_1_1597028971700936 | T | TABLE | 2020-08-10 11:09:31.701033 |
+--------------------------------+---------------+-------+----------------------------+
1 row in set (0.00 sec)
obclient> flashback table t to before drop;
Query OK, 0 rows affected (0.01 sec)
obclient> select * from t;
+----+------+
| id | k |
+----+------+
| 1 | 1 |
+----+------+
1 row in set (0.00 sec)
