在10g以前是没有这个功能的，删除后就没有了，只能从备份恢复。
＃将执行的操作记录在文件中，就是屏幕输出的内容
SQL>spool '/home/oracle/drop.txt'
＃＃删除 emp表
SQL> drop table emp ;
Table dropped.
＃现在查看emp已经不存在了
SQL> select * from emp ;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
＃查看回收站的内容
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$sGRMOtFvhCPgQKjABwAOsw==$0 TABLE 2011-10-29:06:20:34
＃查看用户回收站的内容
SQL> select * from user_recyclebin
2 ;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$sGRMOtFvhCPgQKjABwAOsw==$0 EMP DROP
TABLE USERS 2005-06-30:19:47:57
2011-10-29:06:20:34 517667 YES YES
51148 51148 51148 8
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$sGRMOtFuhCPgQKjABwAOsw==$0 PK_EMP DROP
INDEX USERS 2005-06-30:19:47:57
2011-10-29:06:20:34 517663 NO YES
51148 51148 51149 8
将回收站的表恢复出来
SQL> flashback table emp to before drop ;
Flashback complete.
再看回收站就没有东西了
SQL> show recyclebin
emp现在又可以用了
SQL> select * from emp where sal > 3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
SQL> spool off
[root@bogon oracle]#
相关知识点：
1、从回收站恢复时重命名表
SQL> flashback table t2 to before drop rename to t4 ;
2、删除回收站指定的表
SQL> purge table t4;
3、清空回收站
SQL> purge recyclebin
4、删除表时不经过回收站直接删除
SQL> drop table t3 purge ;
＃将执行的操作记录在文件中，就是屏幕输出的内容
SQL>spool '/home/oracle/drop.txt'
＃＃删除 emp表
SQL> drop table emp ;
Table dropped.
＃现在查看emp已经不存在了
SQL> select * from emp ;
select * from emp
*
ERROR at line 1:
ORA-00942: table or view does not exist
＃查看回收站的内容
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP BIN$sGRMOtFvhCPgQKjABwAOsw==$0 TABLE 2011-10-29:06:20:34
＃查看用户回收站的内容
SQL> select * from user_recyclebin
2 ;
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$sGRMOtFvhCPgQKjABwAOsw==$0 EMP DROP
TABLE USERS 2005-06-30:19:47:57
2011-10-29:06:20:34 517667 YES YES
51148 51148 51148 8
OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
TYPE TS_NAME CREATETIME
------------------------- ------------------------------ -------------------
DROPTIME DROPSCN PARTITION_NAME CAN CAN
------------------- ---------- -------------------------------- --- ---
RELATED BASE_OBJECT PURGE_OBJECT SPACE
---------- ----------- ------------ ----------
BIN$sGRMOtFuhCPgQKjABwAOsw==$0 PK_EMP DROP
INDEX USERS 2005-06-30:19:47:57
2011-10-29:06:20:34 517663 NO YES
51148 51148 51149 8
将回收站的表恢复出来
SQL> flashback table emp to before drop ;
Flashback complete.
再看回收站就没有东西了
SQL> show recyclebin
emp现在又可以用了
SQL> select * from emp where sal > 3000;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7839 KING PRESIDENT 17-NOV-81 5000
10
SQL> spool off
[root@bogon oracle]#
相关知识点：
1、从回收站恢复时重命名表
SQL> flashback table t2 to before drop rename to t4 ;
2、删除回收站指定的表
SQL> purge table t4;
3、清空回收站
SQL> purge recyclebin
4、删除表时不经过回收站直接删除
SQL> drop table t3 purge ;
本文转自zhaoyun00 51CTO博客，原文链接:http://blog.51cto.com/zhaoyun/700567
