20 September 2011

How to recover Oracle Dropped Table


[oracle@testserver db_1]$ sqlplus scott/oracleSQL> select * from emp;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
SCOTT BIN$p1LI8Mt6uzXgQAB/AQAetw==$0
EMP DROP TABLE
USERS 2005-10-22:22:23:20 2011-07-05:18:10:12
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
636508 YES YES 51151 51151
51151 8
SQL>drop table EMP;
Table dropped.
SQL> select * from EMP;
select * from EMP
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>conn sys/oracle as sysdba ---- Login as sysdba
Connected.
SQL> FLASHBACK TABLE SCOTT.EMP TO BEFORE DROP;
Flashback complete.
SQL> conn scott/oracle
Connected.
SQL>select * from emp;
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
SCOTT BIN$p1LI8Mt6uzXgQAB/AQAetw==$0
EMP DROP TABLE
USERS 2005-10-22:22:23:20 2011-07-05:18:10:12
OWNER OBJECT_NAME
------------------------------ ------------------------------
ORIGINAL_NAME OPERATION TYPE
-------------------------------- --------- -------------------------
TS_NAME CREATETIME DROPTIME
------------------------------ ------------------- -------------------
DROPSCN PARTITION_NAME CAN CAN RELATED BASE_OBJECT
---------- -------------------------------- --- --- ---------- -----------
PURGE_OBJECT SPACE
------------ ----------
636508 YES YES 51151 51151
51151 8
SQL>

No comments: