How to Drop Oracle 10g 11g Bin$ / Recycle bin Tables

advertisements

_____________________________________________________________________________________________________________________

In Oracle 10g and 11g if you do a select * from tab; you can see many tables starts with Bin$ which we cannot drop using drop table command. These are the objects in the recycle bin which can be used for recovery purpose in case needed.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------

BIN$tj3fLpbfbJPgQAwKjWcsOg==$0 TABLE

BIN$u9W85aj08IDgQAwKjWcotg==$0 TABLE

BIN$u9W85aj18IDgQAwKjWcotg==$0 TABLE

BIN$u9W85aj28IDgQAwKjWcotg==$0 TABLE

BIN$u9W85ajz8IDgQAwKjWcotg==$0 TABLE

BIN$vDP7Mc1tUD/gQAwKjWcJsw==$0 TABLE

CANDIDATE                      TABLE

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME

---------------- ------------------------------ ------------ -------------------

CANDIDATE        BIN$tj3fLpbfbJPgQAwKjWcsOg==$0 TABLE        2012-01-11:04:01:05

EMP              BIN$u9W85aj28IDgQAwKjWcotg==$0 TABLE        2012-03-22:09:22:47

EMP              BIN$u9W85aj18IDgQAwKjWcotg==$0 TABLE        2012-03-22:09:21:09

EMP              BIN$u9W85aj08IDgQAwKjWcotg==$0 TABLE        2012-03-22:09:19:25

EMP              BIN$u9W85ajz8IDgQAwKjWcotg==$0 TABLE        2012-03-22:09:18:03

TEST             BIN$vDP7Mc1tUD/gQAwKjWcJsw==$0 TABLE        2012-03-27:02:02:36

These tables / recycle bin objects needs to be purged from the recycle bin. Once you purge from the recycle bin it won’t appear in the select * from tab command also.

SQL> purge recyclebin;

Recyclebin purged.

SQL> show recyclebin

How to drop a table without keeping the table in Recycle bin? You have to use purge keyword with drop command.

SQL> drop table TEST1 purge;

Table dropped.

SQL> show recyclebin

_____________________________________________________________________________________________________________________

Website Stats