Recycle bin in oracle database

[Norse]

I used to read my oracle book and got the word "Recycle bin".What is the use of recycle bin in oracle database.Is it related to the windows recycle bin or something else. The book that I used,only mentioned with "Recycle Bin".Would be tell me more about this concept.I am waiting for your reply..

[Reegan]

Recycle bin in oracle database :

The recycle is nothing but it is an data dictionary table which is used to store the information about the dropped object by the database.After dropping the table and associated objects like indexes, constraints, nested tables are not dropped permanently and still contains some space in memory.

A user can view the deleted objects in the recycle bin using following statement

SELECT * FROM RECYCLEBIN;

[opaper]

Enabling and disabling Recycle bin :

You can enable and disable the recycle bin for your database using specified parameters for recycle bin.During disable mode of your database recycle bin,the deleted objects would be permanently deleted from memory and you need to recover it using RECOVER Command.

The recycle bin is enabled by default.To disable the recycle bin setup this parameter :

ALTER SESSION SET recyclebin = OFF;

And change whenever you want to use this facility using :

ALTER SESSION SET recyclebin = ON;

[MindSpace]

Viewing and Querying Objects in the Recycle Bin :

You can view and make some queries to investigation about your deleted objects using provided views from the database:

1- USER_RECYCLEBIN
It is used by particular user to get the information about deleted object.you can select your view choices according to the columns using DESC command.

2- DBA_RECYCLEBIN
It is only used by the database Administrator to query for the deleted objects .

[Modifier]

Restoring tables from Recycle bin :

To restore the dropped objects from the recycle bin,you need to use FLASHBACK query.You can indicate the name of the table to recover it through recycle bin.An optional facility is provided you to recover your table with different name.To execute the recover flashback command you need to have the same privilege as you need to drop.

To restore a table from recycle bin with a new name,execute the following SQL query which will restores emp_product_record table and assigns to it a new name:

FLASHBACK TABLE emp_product_record TO BEFORE DROP 
   RENAME TO emp_product_record_new;