FlashBack Conecpt: Recover the droped tables from recyclebin

What is Recycle Bin
Oracle has introduced "Recycle Bin" Feature Oracle 10g to store all the dropped objects.
If any table in Oracle 10g is dropped then any associated objects to this table such as indexes,
constraints and other dependant objects are simply renamed with a prefix of BIN$$.

Why Recycle Bin
A user drops a very important table--accidentally, of course--and it needs to be revived as soon as possible.
Oracle9i Database introduced the concept of a Flashback Query option to retrieve data from a point in time in the past, but it can't flash back DDL operations such as dropping a table.
The only recourse is to use tables pace point-in-time recovery in a different database and then recreate
the table in the current database using export/import or some other method.


How to Enable/Disable Recycle Bin
++++++++++++++++++++++++++++++++++++++

1. 1st Check the status of it from the inti.ora file (parameter file)

Mostly it is in the on status

SQL > SELECT Value FROM V$parameter WHERE Name = 'recyclebin';

Value
-----
On


SQL> show parameter recyclebin;

NAME                       TYPE        VALUE
-------------------------- ----------- -----------------------------
recyclebin                 string      ON


if the Value Is “on” then recyclebin feature is enabled for the Database.
If the Value is “off” the recyclebin feature is disabled.

The following commands are used to enable or Disable the Feature


SQL > ALTER SYSTEM SET recyclebin = ON;
or
SQL > ALTER SESSION SET recyclebin = ON;

SQL > ALTER SYSTEM SET recyclebin = OFF;
or
SQL > ALTER SESSION SET recyclebin = OFF;


Show the Contents in RECYCLEBIN  (there are 3 things recycclebin,user_recyclebin and dba_recyclebin)
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

user_reyclebin and recyclebin is same
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@


Use the following commands to show all the objects that are stored in Recycle Bin,

SQL > SHOW RECYCLEBIN;
Or
SQL > SELECT * FROM USER_RECYCLEBIN;


Steps to recover the dropped table

For example I have dropped a table(khatai1) from khatai schema. 

SQL> show user
USER is "KHATAI"

SQL> select * from recyclebin;  --> nothing is present, recyclebin is empty

no rows selected
SQL> drop table khatai1;

Table dropped.

SQL> desc khatai1;   
ERROR:
ORA-04043: object khatai1 does not exist  ----> Because table was dropped on previous step.


Now again check the recyclebin for finding out is there any object is present or not

 SQL> select * from recyclebin;

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION TYPE
                    TS_NAME                        CREATETIME          DROPTIME
              DROPSCN PARTITION_NAME                   CAN CAN    RELATED BASE_O
BJECT PURGE_OBJECT      SPACE
------------------------------ -------------------------------- --------- ------
------------------- ------------------------------ ------------------- ---------
---------- ---------- -------------------------------- --- --- ---------- ------
----- ------------ ----------
BIN$lKNkXsCSQu6Vrzj6BIGwcA==$0 KHATAI1                          DROP      TABLE
                    USERS                          2015-07-15:14:47:27 2015-07-2
1:14:57:47    1364484                                  YES YES      74578
74578        74578          8

SQL>select droptime, dropscn from user_recyclebin where original_name='KHATAI1';


Now recover steps

SQL> flashback table khataid to before drop;

flashback complete  -----> and you are done.

Please check the below image



If you see the recyclebin is now empty because the table was recovered.

Note: All the tables that are created by user sys is in system tablespace and every table dropped from the system tablespace is not going to dba_recyclebin or recycle-bin.So be careful while droping the system tables.


Hope you are benefited.. thanks.. keep enjoying..