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.
2 comments:
Why go through all of that work when a simple
purge dba_recyclebin;
when connected with SYSDBA privileges does the same thing in this example?
thanks, that's a nice catch, that should've been Method-1...
Post a Comment