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

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
---------------- ------------------------------ ------------ -------------------
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
DBA Tips Data Pump Reference


Post a Comment


dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google