描述

用于从回收站中恢复被删除的 TABLE。

前置条件

回收站需要处于开启状态,可以通过show variables like 'recyclebin';来查看回收站是否开启。

  1. obclient> show variables like 'recyclebin';
  2. +---------------+-------+
  3. | Variable_name | Value |
  4. +---------------+-------+
  5. | recyclebin | ON |
  6. +---------------+-------+
  7. 1 row in set (0.00 sec)

如果回收站处于关闭状态,可以通过set recyclebin = on;来开启。回收站中的表没有被实际删除,仍然会占用资源,如果需要彻底删除,可以执行purge recyclebin;

格式

  1. FLASHBACK TABLE object_name TO BEFORE DROP [RENAME to db_name.table_name];

参数解释

参数

描述

object_name

指定要恢复的object名称或表名,只有在表所在的database中才能执行。恢复TABLE时,也会将和TABLE相关的INDEX也恢复出来。

RENAME to

修改表名和表所属的库。

示例

  • 从回收站中恢复被删除的表 t。
  1. obclient> create table t(id int primary key, k int);
  2. Query OK, 0 rows affected (0.04 sec)
  3. obclient> insert into t values(1,1);
  4. Query OK, 1 row affected (0.00 sec)
  5. obclient> select * from t;
  6. +----+------+
  7. | id | k |
  8. +----+------+
  9. | 1 | 1 |
  10. +----+------+
  11. 1 row in set (0.00 sec)
  12. obclient>> drop table t;
  13. Query OK, 0 rows affected (0.01 sec)
  14. obclient> select * from t;
  15. ORA-00942: table or view 'SYS.T' does not exist
  16. obclient> show recyclebin;
  17. +--------------------------------+---------------+-------+----------------------------+
  18. | OBJECT_NAME | ORIGINAL_NAME | TYPE | CREATETIME |
  19. +--------------------------------+---------------+-------+----------------------------+
  20. | __recycle_$_1_1597028971700936 | T | TABLE | 2020-08-10 11:09:31.701033 |
  21. +--------------------------------+---------------+-------+----------------------------+
  22. 1 row in set (0.00 sec)
  23. obclient> flashback table t to before drop;
  24. Query OK, 0 rows affected (0.01 sec)
  25. obclient> select * from t;
  26. +----+------+
  27. | id | k |
  28. +----+------+
  29. | 1 | 1 |
  30. +----+------+
  31. 1 row in set (0.00 sec)