ORA-38301: can not perform DDL/DML over objects in Recycle Bin

同事问删除一张名字很怪的表，怎么删除不掉。

错误信息是ORA-38301: can not perform DDL/DML over objects in Recycle Bin

 

一看他的sql原来是在drop 一张回收站中的表，对回收站中的表只能purge ，不能drop

 

 

SQL> DROP TABLE t_drop;

Table dropped

 

SQL> SELECT r.object_name,r.original_name,r.operation,r.droptime FROM user_recyclebin r;

OBJECT_NAME                    ORIGINAL_NAME             OPERATION    DROPTIME
------------------------------ --------------------------------    ---------          -------------------
BIN$oPOJAsoYIArgQwoBAQIgCg==$0 T_DROP           DROP           2011-04-15:18:30:48

 

 

SQL> drop table "BIN$oPOJAsoYIArgQwoBAQIgCg==$0";

drop table "BIN$oPOJAsoYIArgQwoBAQIgCg==$0"

ORA-38301: can not perform DDL/DML over objects in Recycle Bin

 

SQL> purge table "BIN$oPOJAsoYIArgQwoBAQIgCg==$0";

Done

 

SQL> SELECT r.object_name,r.original_name,r.operation,r.droptime FROM user_recyclebin r;

OBJECT_NAME                    ORIGINAL_NAME                 OPERATION            DROPTIME
------------------------------        -------------------------------- --------------           -------------------

 

-- 也可以purge表的原始名称。

SQL> purge table  T_DROP     ;

 

 

如果不想永久删除表，不想放在回收站中可以在删除的时候加purge参数。

 

SQL>drop table &table_name  purge;