Recycle Bin in Oracle Database
Introduction
In windows have a recycle bin to all deleted files will be store like wise in oracle database also
provided recycle bin which keeps 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 Recycle bin which will not work sys owned objects it will worked on user objects only
If we drop a table using purge command, tables willbe removed completely (even from recycle bin also)
How to check the recycle bin is on or off
1.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 the table is there in the recycle bin or not
SQL> drop table chaitanya.CHAITUTABLE;
Table purged.
SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';
OWNER OBJECT_NAME ORIGINAL_NAME DROPTIME CAN
------------------ --------------------------------------------- ------------------ ------------------- ---
CHAITANYA BIN$fxhnqWVcPLTgVAAQ4B8y7Q==$0 CHAITUTABLE 2020-01-10:12:45:03 YES
Now the table is in recycle bin we can recover the table if required
3. For purging the table for recyclebin
in order to remove the table from recyclebin also
SQL> purge table chaitanya.CHAITUTABLE;
Table purged.
SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUTABLE';
no rows selected
4. To purge complete recyclebin
SQL> select count(*) from dba_recyclebin;
COUNT(*)
----------
102
SQL> purge recyclebin;
Recyclebin purged.
SQL> select count(*) from dba_recyclebin;
COUNT(*)
----------
0
5. To drop a table without keeping in recyclebin
SQL> select count(*) from CHAITANYA.chaitusample;
COUNT(*)
----------
82269
SQL> drop table CHAITANYA.CHAITUSAMPLE purge;
Table dropped.
SQL> select owner,OBJECT_NAME,ORIGINAL_NAME,DROPTIME,CAN_UNDROP from dba_recyclebin where ORIGINAL_NAME='CHAITUSAMPLE';
no rows selected
Note : Info on Recycle bin in oracle it may be differ in your encironment like production, testing,development and naming conventions
THANKS FOR VIEWING MY BLOG FOR MORE UPDATES FOLLOW ME OR SUBSCRIBE ME
