Oracle Recycle Bin

v  Recycle bin: Oracle recycle bin is a new feature introduced with the new versions of oracle databases, Used to enable -FLASHBACK DROP feature.
§   How to disable and enable recycle bin
alter system set recyclebin  = off;
alter session set recyclebin  = off;
§   Views to Manage recyclebin
o    User_recyclebin
o    Dba_recyclebin
§   SQL> show recyclebin -- will show details of the recycle bin in your database.
§   How can we permanently remove objects from recycle bin
Use the purge command
Purge table <table_name>      -- removes particular table permanently from the database.
Purge recyclebin;   -- remove all the objects from recyclebin
§   How to restore objects from recycle bin
Flashback table <table_name>  to before drop
§   Query  to view the contents of recyclebin
SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME TYPE
§   Run the following query to view if the table “JOB_HISTORY” retains the system generated recyclebin names
SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';
INDEX_NAME
------------------------------
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0
§   Restore the original names of the first two indexes.
ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;
        Double quotes are required around the system-generated names.
§   Show the recyclebin details
SQL> show recyclebin
 
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES        BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE        2003-10-27:14:00:19
 
        Query recyclebin to find out the objects in the recyclebin.
 
SQL>SELECT * FROM "BIN$yrMKlZaVMhfgNAgAIMenRA==$0";   -- In double quotes
§   You can use the PURGE statement to purge all the objects in the recycle bin that are from a specified table space or only the table space objects belonging to a specified user, as shown in the following examples:
PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;  -- only deletes the table space object belonging to the user oe;
§   Users can purge the recycle bin of their own objects, and release space for objects, by using the following statement:
SQL>Purge recyclebin
        If you have the SYSDBA privilege, then you can purge the entire recycle bin by specifying DBA_RECYCLEBIN, instead of RECYCLEBIN in the previous statement.
§   SQL>purge dba_recyclebin
§   The following example restores int_admin_emp table and assigns to it a new name:
 
 
FLASHBACK TABLE int_admin_emp TO BEFORE DROP RENAME TO int2_admin_emp;