RECYCLE BIN

Oracle has introduced the RECYCLE BIN which is a logical entity to hold all the deleted objects and works exactly like the recycle bin provided in Windows operating system for example. All the deleted objects are kept n the recycle bin; these objects can be retrieved from the recycle bin or deleted permanently by using the PURGE command. Either an individual object like a table or an index can be deleted from the recycle bin

Related Data Dictionary Objects

·         recyclebin$
·         dba_recyclebin   
·         recyclebin 
·         user_recyclebin

user_recyclebin and dba_recyclebin are use for recovery using flashback

 
SQL> SELECT name, value
FROM v$parameter
WHERE name LIKE 'recyc%';

NAME             VALUE       
----------------------                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
recyclebin       on       
 

How to Use Recycle Bin in Oracle 10g

Starting and stopping the recyclebin

Syntax: 
ALTER SYSTEM SET recyclebin=<OFF | ON> SCOPE=<BOTH | MEMORY | SPFILE>;

By default recyclebin is set to on

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from  dba_recyclebin;
no rows selected

SQL> drop table scott.emp;
Table dropped.

SQL> select OBJECT_NAME, ORIGINAL_NAME, TYPE from dba_recyclebin;
OBJECT_NAME                              ORIGINAL_NAME        TYPE
-----------------------------------------------------------------
BIN$iSsOuMCxThKB65n/ep2Y5g==$0    PK_EMP        INDEX
BIN$JsuRtykaSpOd8XLplx1/vA==$0    EMP           TABLE

To recover the emp table issue the following command

Syntax:
FLASHBACK TABLE <table_name> TO BEFORE DROP {RENAME TO <new_table_name>};
SQL> flashback table scott.emp to before drop;
Flashback complete.

Now emp table is restored from recyclebin.



You can also rename the table during the time of restore

SQL> flashback table scott.emp to before drop
     RENAME TO scott.emp2; 

To clear the recycle bin issue the following command

Syntax:
PURGE TABLE <recycle_bin_name>;
 
SQL> PURGE TABLE scott.emp;
 
Table purged.

Remove Recycle Bin Objects by Tablespace and User

Syntax:
PURGE TABLESPACE <tablespace_name> 
USER <schema_name>;
 
SQL Code:
PURGE TABLESPACE users USER scott;

Clear full recycilebin

Syntax:
PURGE RECYCLEBIN

Empty Everything in All Recycle Bins

SQL Code:
PURGE dba_recyclebin;