Thursday, November 11, 2010

Clearing the RECYCLEBIN in an Oracle database

How does a DBA clear objects from a recyclebin of another user in an Oracle database ?...It can be done in one of the three ways listed below.



SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,TS_NAME from dba_recyclebin;

OWNER      OBJECT_NAME                    ORIGINAL_NAME     TS_NAME
---------  ------------------------------ ----------------- ----------
USER1      BIN$b5VRkexOdOjgRAADurN2OQ==$0 TABLE1            TBL_SPACE
USER1      BIN$b5VRkexNdOjgRAADurN2OQ==$0 TABLE2            TBL_SPACE
USER1      BIN$b5VRkexMdOjgRAADurN2OQ==$0 TABLE3            TBL_SPACE
USER1      BIN$b5VRkexLdOjgRAADurN2OQ==$0 TABLE4            TBL_SPACE
USER1      BIN$b5VRkexJdOjgRAADurN2OQ==$0 TABLE5            TBL_SPACE
                                                 


Method-1
--------


SQL> purge table user1."BIN$b5VRkexOdOjgRAADurN2OQ==$0";

Table purged.
                                                 


Method-2
--------


SQL> purge table user1."TABLE2";

Table purged.
                                                 


Method-3
--------


SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";
drop table xdb."BIN$lMZuKzEkDFfgRAADurN2OQ==$0"
               *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
                                                 


This is like killing the " mother of recyclebin evil " - we cannot perform DDL/DML on an object in recyclebin as the definition of the error states. The only reason for getting an ORA-38301 in this situation is because the RECYCLEBIN database parameter is turned on and turning it off lets a DBA issue a DROP TABLE command against a recyclebin table but at the cost of not being able to flashback the database to recover a lost table.


SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/app/oracle/product/10.2.0
                                                 /dbs/spfileprod_db.ora

SQL> alter system set recyclebin=off scope=both;

System altered.

SQL> sho parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF


SQL> drop table user1."BIN$b5VRkexMdOjgRAADurN2OQ==$0";

Table dropped.