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

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Feb 17 11:07:27 2010

Copyright (c) 1982, 2004, Oracle. All rights reserved.

Enter user-name: suvin@test
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
STEP 1 : WE ARE GOING TO DROP TABLE TABLE2.
SQL> desc table2;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER(3)

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

STEP 2 : NOW DROP THE TABLE.

SQL> drop table table2;

Table dropped.

SQL> show user
USER is "SUVIN"

SQL> select * from table2;
select * from table2
*
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 "SUVIN"

STEP 2 : CHECK WHEATHER THE TABLE IS AVAILABLE IN RECYCLEBIN.

SQL> select object_name,original_name from user_recyclebin;

OBJECT_NAME ORIGINAL_NAME
------------------------------ --------------------------------
BIN$T0xRBK9YSomiRRmhwn/xPA==$0 TABLE2

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

SQL> flashback table table2 to before drop;

Flashback complete.

SQL> select * from table2;

ID
----------
2
2
3
4
4
5

6 rows selected.

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

IF THE DROPPED TABLE IS NOT IN RECYCLEBIN THEN YOU CANT USE FLASHBACK FEATURE TO RECOVER THE TABLE.

Else, you'll need a Backup or a dump to restore your Table.

For instance, by duplicating your database elsewhere and get back the Table by DataPump (expdp/impdp).