Like windows have recyclebin, Oracle database has also provided recyclebin, which keep all the dropped objects.
When we drop a table(DROP TABLE TABLE_NAME) in the database, the tables will logically be removed, but it still exists in the same tablespace but with a prefix BIN$$. And it will not release the space also.
NOTE – The Recyclebin does not work for SYS owned objects
If we drop the table with purge command, its tables will be removed completely (even from recyclebin).
1. How to check whether recyclebin is on or not?
SQL> show parameter recyclebin; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on SQL> select name,value from v$parameter where name like '%recyclebin%'; NAME VALUE ------------ ------------ recyclebin on
2. Drop a table and check whether recyclebin content:
SQL> drop table dbaclass.TEMPTABLE; Table purged. SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='TEMPTABLE'; OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME CAN ------------------ --------------------------------------------- ------------------ ------------------- --- DBACLASS BIN$fxhnqVWcLPTgVAAQ4B8y7Q==$0 TEMPTABLE 2019-01-10:12:43:03 YES
Now as the table is inside recyclebin, if required, if we recover this.
SEE- > How To Recover A Dropped User Using Flashback Oracle
3. For purging the table for recyclebin.
In order to remove the table from recyclebin also.
SQL> purge table dbaclass.TEMPTABLE; Table purged. SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='TEMPTABLE'; no rows selected
4. To purge complete recyclebin:
SQL> select count(*) from dba_recyclebin; COUNT(*) ---------- 73 SQL> purge recyclebin; Recyclebin purged. SQL> select count(*) from dba_recyclebin; COUNT(*) ---------- 0
5. To drop a table(without keeping in recylebin)
SQL> select count(*) from DBACLASS.TEST2; COUNT(*) ---------- 91138 SQL> SQL> SQL> drop table DBACLASS.TEST2 purge; Table dropped. SQL> SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='TEST2'; no rows selected