Recycle Bin in Oracle Database

 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