How to recover dropped table in Oracle 10g using flashback feature

 

How to recover dropped table in Oracle 10g using flashback feature……………

Enter user-name: fkhalid@malik
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options

STEP 1 : WE ARE GOING TO DROP TABLE test2.
================================
SQL> desc test2;
Name Null? Type
------------ -------- ----------------------------
ID NUMBER(3)

SQL> select * from test2;

ID
----------
1
2
3
4
5
6

6 rows selected.

STEP 2 : NOW DROP THE TABLE.
=====================

SQL> drop table test2;

Table dropped.

SQL> show user
USER is "FKHALID"

SQL> select * from test2;
select * from test2
*
ERROR at line 1:
ORA-00942: table or view does not exist

Now We are confirmed that table has been dropped.

SQL> show user
USER is "FKHALID"

STEP 3 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.
===========================================

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$gGu93nOJ0VrgQ6wUECjRWg==$0 TEST2

STEP 4 : USE THIS FLASHBACK COMMAND TO RECOVER THE DROPPED TABLE.
===============================================

SQL> flashback table test2 to before drop;

Flashback complete.

SQL> select * from test2;

ID
----------
1
2
3
4
5
6 

6 rows selected.

SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit

NOTE: IF THE DROPPED TABLE IS NOT IN RECYCLEBIN THEN YOU CANT USE FLASHBACK FEATURE TO RECOVER THE TABLE.
You need to specify the retention period for flashback logs to retain the tables for longer duration.

Otherwise you'll need a Backup or a dump to restore your table like duplicating your database elsewhere and get back the Table by DataPump (expdp/impdp).