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

drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。这样，回收站里的表信息就可以被恢复，或彻底清除。 

1.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句

flashback table <user_recyclebin.object_name or user_recyclebin.original_name> to before drop [rename to <new_table_name>];

将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。

若要彻底删除表，则使用语句：drop table <table_name> purge;

2.清除回收站里的信息

清除指定表：purge table <table_name>;

清除当前用户的回收站：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

Oracle 10g 中出现表名：BIN$2cMp4FjwQ2Cw3Lj+BxLYTw==$0 最近发现Oracle中出现了这些奇怪的表名，上网查找后发现是oracle10g的回收站功能，并没有彻底的删除表，而是把表放入回收站，最后就出现了这样一堆奇怪的表名。。。。

一、清除的方法如下：

　　1、purge table origenal_tableName;

           purge index origenal_indexName;

      2、PURGE recyclebin;

二、查询垃圾信息，可以用如下SQL语句：

　　SELECT t.object_name,t.type ,t.original_name FROM user_recyclebin t;

三、删除Table不进入Recycle的方法：

　　drop table tableName purge;