From Oracle 10g if we try to drop a table when the recycle mode is ON then it just gets renamed – and does not gets deleted. So, the space occupied doesn’t get purged.
DROP TABLE test_readonly;
– Note when a table is dropped it is just renamed. It is not droped when the RECYCLE mode is ON. So the space occupied is there as it is.
SQL> select object_name, original_name, operation from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME OPERATION
—————————— ——————————– ———
BIN$uCJ5kSosTM+s7chj6cJeGQ==$0 TEST_READONLY DROP
BIN$wl9CUjW7RsapzkrsoHDm+w==$0 TEST_READONLY DROP
Note: TEST_READONLY was dropped, recreated then dropped again.
To drop a table without storing it in recyclebin -
SQL> DROP TABLE test_Readonly purge;
SQL> select * from user_recyclebin;
no rows selected
To purge the table that was dropped
PURGE TABLE “BIN$wl9CUjW7RsapzkrsoHDm+w==$0”
To purge the recyclebin