Oracle : Flashback Technology / Recycle Bin

A wonderful feature in Oracle 10g is "Flashback Technology".

What Flashback is?
 Flashback is a feature in Oracle 10g, through which you can recover accidently dropped tables, packages, rows/data. When an object is dropped in Oracle database, then it is stored in a container 'Recycle Bin' (just like Windows).From this Recycle-bin objects can be recovered; unless permanently purged.

What is Recycle Bin (In Oracle Flashback)?
 Recycle Bin is a container in Oracle database, which stores all dropped objects. We can say this is a virtual container, because dropped objects (like tables, indexes, constraints, packages etc) are not moved here, rather it just stores their information & dropped objects are renamed with prefix "BIN$$"

To get information about all dropped objects following query can be used :-

SELECT * FROM RECYCLEBIN;

 It has original object name in 'ORIGINAL_NAME' column, dropped date/time in 'DROPTIME' column, and type of object (like TABLE, INDEX etc) in 'TYPE' column. Following is table structure of RECYCLEBIN:

Column Name	ID	Data Type	Null?
OBJECT_NAME	1	VARCHAR2 (30 Byte)	N
ORIGINAL_NAME	2	VARCHAR2 (32 Byte)	Y
OPERATION	3	VARCHAR2 (9 Byte)	Y
TYPE	4	VARCHAR2 (25 Byte)	Y
TS_NAME	5	VARCHAR2 (30 Byte)	Y
CREATETIME	6	VARCHAR2 (19 Byte)	Y
DROPTIME	7	VARCHAR2 (19 Byte)	Y
DROPSCN	8	NUMBER	Y
PARTITION_NAME	9	VARCHAR2 (32 Byte)	Y
CAN_UNDROP	10	VARCHAR2 (3 Byte)	Y
CAN_PURGE	11	VARCHAR2 (3 Byte)	Y
RELATED	12	NUMBER	N
BASE_OBJECT	13	NUMBER	N
PURGE_OBJECT	14	NUMBER	N
SPACE	15	NUMBER	Y

How to recover objects using Oracle Flashback technology (From Oracle Recycle bin)?

Recovering objects from Oracle Recycle Bin is interesting: Let's take an example:

1. 
   
   Create a table & Drop it:
   --Create a dummy table for testing purpose
   CREATE TABLE testing_oracle_flashback
   AS
   SELECT * 
   FROM user_objects
   WHERE ROWNUM =10;
   /
   
   
   SELECT * FROM testing_oracle_flashback;
   /
   
   
   --Drop the table 
   DROP TABLE testing_oracle_flashback;
   /
   
   
   SELECT * FROM testing_oracle_flashback;
   /
   --There will be error that table or view does not exist.
   
   
   
   
2. 
   
   Check Recycle Bin:
   SELECT *
   FROM   RECYCLEBIN
   WHERE  UPPER(TYPE) = 'TABLE'
   AND    UPPER(original_name) = 'TESTING_ORACLE_FLASHBACK';
   
   
3. 
   
   Recover Table through Oracle Flashback (To Previously Dropped Version – with same name)
   FLASHBACK TABLE testing_oracle_flashback TO BEFORE DROP; 
   
   
4. 
   
   Recover Table through Oracle Flashback (To Previously Dropped Version – With different name)
   FLASHBACK TABLE testing_oracle_flashback TO  BEFORE DROP RENAME TO testing_or_flashback_1;
   
   
5. 
   
   Recover Table through Oracle Flashback (To any given DateTime)
   FLASHBACK TABLE testing_oracle_flashback TO TIMESTAMP ('MAR-10-2010, 02:33:00');
   
   

For further details, please visit :
http://srinisreeramoju.blogspot.com/2010/03/oracle-flashback-technology-recovering.html


For best oracle articles, visit:
http://srinisreeramoju.blogspot.com/

Enjoy ....

Oracle, SQL, PL/SQL, Flashback, FLASH BACK, Oracle Flash back technology, Oracle Flashback Technology, Flashback Technology, Recyclebin, Oracle Recyclebin, RECYCLEBIN, purge table, recover oracle tables, recover oracle objects, recover dropped objects, recover dropped tables, oracle dba, table recovery, oracle constraints recovery