oracle闪回试验

不能使用sysdba，system闪回，要用创建这个表的用户

SQL> drop table emp_test;
Table dropped

SQL> select owner,object_name,original_name, DROPTIME from dba_recyclebin order by droptime;
OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME

---

SCOTT BIN$rtpdTNe6SIysmO+ZB0t3aQ==$0 EMP_TEST 2006-10-26:22:23:06
SCOTT BIN$roQhkx6tQneThvaRlsjrhw==$0 EMP_TEST 2006-10-26:22:23:50

–使用flashback table 进行恢复,默认恢复最近删除的表
SQL> flashback table emp_test to before drop;
Done

SQL> select owner,object_name,original_name,DROPTIME from dba_recyclebin order by droptime;
OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME

---

SCOTT BIN$rtpdTNe6SIysmO+ZB0t3aQ==$0 EMP_TEST 2006-10-26:22:23:06

–也可以指定表名进行恢复
SQL> flashback table “BIN$rtpdTNe6SIysmO+ZB0t3aQ==$0” to before drop;
flashback table “BIN$rtpdTNe6SIysmO+ZB0t3aQ==$0” to before drop
ORA-38312: 原始名称已被现有对象使用

此时被恢复的表名称仍然采用以前的名字，我们之前已经恢复一次EMP_TEST,所以现在恢复就出现了重名，不过可以为其指定新的名字。

SQL> flashback table “BIN$rtpdTNe6SIysmO+ZB0t3aQ==$0” to before drop rename to emp_test2;
Done

SQL> select table_name from user_tables where table_name like ‘%EMP_TEST%’;

TABLE_NAME

EMP_TEST
EMP_TEST2

–删除回收站里的对象，不能使用DROP命令进行删除，必须使用PURGE命令
SQL> drop table emp_test;
Table dropped

SQL> drop table emp_test2;
Table dropped

SQL> select owner,object_name,original_name,DROPTIME from dba_recyclebin order by droptime;
OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME

---

SCOTT BIN$M4Q0Pb94SOWSFGarOpI5Og==$0 EMP_TEST 2006-10-26:22:46:07
SCOTT BIN$7P+osQdjSs+5CcSXBc0NAA==$0 EMP_TEST2 2006-10-26:22:46:10

SQL> drop table “BIN$M4Q0Pb94SOWSFGarOpI5Og==$0”;
drop table “BIN$M4Q0Pb94SOWSFGarOpI5Og==$0”
ORA-38301: 无法对回收站中的对象执行 DDL/DML

SQL> purge table “BIN$M4Q0Pb94SOWSFGarOpI5Og==$0”;
Done

SQL> select owner,object_name,original_name,DROPTIME from dba_recyclebin order by droptime;
OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME

---

SCOTT BIN$7P+osQdjSs+5CcSXBc0NAA==$0 EMP_TEST2 2006-10-26:22:46:10

–删除整个回收站里的对象
SQL> purge recyclebin;
Done