oracle默认不启动闪回数据库
如果需要启动闪回数据库，数据库需要设置为归档模式，并启用恢复区。
1、查看是否启动闪回删除
SQL> show parameter recyclebin; NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string on
启动闪回
2、测试闪回删除的表：
创建两个一样的表，然后都删除，进行恢复。
SQL> create table edu.test_flash(id number(12),name varchar2(20)); Table created SQL> insert into edu.test_flash values(1,''); 1 row inserted SQL> commit; Commit complete SQL> drop table edu.test_flash; Table dropped SQL> create table edu.test_flash(id number(12),name varchar2(20)); Table created SQL> insert into edu.test_flash values(2,''); 1 row inserted SQL> commit; Commit complete SQL> drop table edu.test_flash; Table dropped
查看回收站
SQL> select owner,original_name,object_name,ts_name,droptime from dba_recyclebin where owner='EDU'; OWNER ORIGINAL_NAME OBJECT_NAME TS_NAME DROPTIME
------------------------------ -------------------------------- ------------------------------ ------------------------------ -------------------
EDU TEST_FLASH BIN$MFwmGr6PC/LgUwEAAH94lA==$0 USERS 2016-04-13:18:22:02
EDU TEST_FLASH BIN$MFwmGr6QC/LgUwEAAH94lA==$0 USERS 2016-04-13:18:22:31
恢复删除的表
SQL> flashback table edu.test_flash to before drop; Done
再次查看回收站
SQL> select owner,original_name,object_name,ts_name,droptime from dba_recyclebin where owner='EDU'; OWNER ORIGINAL_NAME OBJECT_NAME TS_NAME DROPTIME
------------------------------ -------------------------------- ------------------------------ ------------------------------ -------------------
EDU TEST_FLASH BIN$MFwmGr6PC/LgUwEAAH94lA==$0 USERS 2016-04-13:18:22:02
发现直接按照名字进行闪回，闪回的是最后删除的表
如果需要指定闪回哪个表还可以执行：
flashback table "BIN$MFwmGr6PC/LgUwEAAH94lA==$0" to before drop rename to test_flash_2;