Sunday, August 30, 2020

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

1 comment:

ITIL Process

ITIL Process Introduction In this Blog i am going to explain  ITIL Process, ITIL stands for Information Technology Infrastructure Library ...