All Products
Search
Document Center

FLASHBACK TABLE BEFORE DROP

Last Updated: Jun 18, 2021

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)