Flashback DROP Table In Oracle 10g. (Recover Your Droped Table )

In Oracle database 10g default DROP option to move table in the recycle bin (or rename it). The DROP TABLE ... PURGE option can be used to permanently drop a table. If you drop table without  using DROP TABLE RAJIB PURGE you can recover your table using Oracle flashback Technology.


SQL> CREATE TABLE FLASHBACK_TEST(ID NUMBER, NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(10,'RAJIB');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(20,'KING');

1 row created.

SQL> INSERT INTO FLASHBACK_TEST(ID, NAME) VALUES(30,'MORGAN');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> SELECT TO_TIMESTAMP(SYSDATE) FROM DUAL;

TO_TIMESTAMP(SYSDATE)
---------------------------------------------------------------------------
27-JUL-13 12.00.00 AM

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2047418

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL> DROP TABLE  FLASHBACK_TEST;

Table dropped.

SQL> SELECT * FROM FLASHBACK_TEST;
SELECT * FROM FLASHBACK_TEST
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> SHOW RECYCLEBIN;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_TEST   BIN$JTA6PK8nRqeAuj79hlI9dQ==$0 TABLE        2013-07-27:12:40:36

SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP;
FLASHBACK TABLE  FLASHBACK_TEST TO BEFOURE DROP
                                   *
ERROR at line 1:
ORA-00905: missing keyword


SQL> FLASHBACK TABLE  FLASHBACK_TEST TO BEFORE DROP;

Flashback complete.

SQL> SELECT * FROM FLASHBACK_TEST;

        ID NAME
---------- --------------------
        10 RAJIB
        20 KING
        30 MORGAN

SQL>