Oracle10g 回收站及彻底删除table:drop table xx purge

drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或彻底清除。
1.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句
flashback table to before drop [rename to ];

将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。
若要彻底删除表，则使用语句：drop table purge;

2.清除回收站里的信息
清除指定表：purge table ;
清除当前用户的回收站：purge recyclebin;
清除所有用户的回收站：purge dba_recyclebin;

===============================================================================
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as test
SQL> select * from test1;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> create table test2 as select * from test1;s
Table created
SQL> select * from test2;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> drop table test2;
Table dropped
SQL> select object_name, original_name, operation, type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE
------------------------------ -------------------------------- --------- -------------------------
BIN$g5jFmA/OShC6+wsWKJiv2w==$0 TEST1 DROP TABLE
BIN$vQwemDg4R9mK9fYJNdYzvg==$0 TEST2 DROP TABLE
SQL> flashback table test2 to before drop rename to test3;--【to test3】将表重命名
Done
SQL> select * from test3;
A B C
-- -- ----------
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> select * from test2;
select * from test2
ORA-00942: 表或视图不存在
--彻底删除表
SQL> drop table test3 purge;
Table dropped
SQL> select * from user_recyclebin where original_name = 'TEST3';
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ------- -------------------------------- ---------- --------- ------- ----------- ------------ -----
SQL> select * from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE
------------------------------ -------------------------------- --------- ------------------------- ------------------------------ ------------------- ------------------- ------- -------------------------------- ---------- --------- ------- ----------- ------------ -----
BIN$g5jFmA/OShC6+wsWKJiv2w==$0 TEST1 DROP TABLE TP_TEST1 2007-08-23:07:57:28 2007-08-23:07:58:51 1411156 YES YES 53086 53086 53086 896
--清除回收站里的表信息test1
SQL> purge table test1;
Done
SQL> select * From user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION TYPE TS_NAME CREATETIME DROPTIME DROPSCN PARTITION_NAME CAN_UNDROP CAN_PURGE RELATED BASE_OBJECT PURGE_OBJECT SPACE

Ref: http://blog.csdn.net/wildwave/archive/2010/01/12/5180273.aspx

+++++++++++++++++++++++++++++

SQL> desc tt
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------

ID NUMBER(38)

SQL> select * from tt;

ID
----------
100
100
3
4

SQL> drop table tt;

表已删除。

SQL> create table tt(id int , name varchar2(10));

表已创建。

SQL> drop table tt;

表已删除。

SQL> create table tt(id int , name varchar2(10) , time date);

表已创建。

SQL> drop table tt;

表已删除。

SQL> select original_name , droptime from dba_recyclebin where original_name='TT
' ORDER BY DROPTIME;

ORIGINAL_NAME DROPTIME
-------------------------------- -------------------
TT 2007-09-21:20:17:59
TT 2007-09-21:20:18:31
TT 2007-09-21:20:18:47

SQL> PURGE TABLE TT;

表已清除。

SQL> select original_name , droptime from dba_recyclebin where original_name='TT
' ORDER BY DROPTIME;

ORIGINAL_NAME DROPTIME
-------------------------------- -------------------
TT 2007-09-21:20:18:31
TT 2007-09-21:20:18:47

SQL>

结论：recyclebin中存在同名对象时，执行purge table table_name时，最先被删除的对象先被从recyclebin中释放！

ref: http://warehouse.itpub.net/post/777/397864