Friday 7 September 2012

How to recover dropped table in 10g onwords?

Today, will look into how to recover deleted table?
10G Onwords Oracle introudces new feature of recycbin. ( Same concept as windows recyclebin)
Suppose from XYZ schema accidentely you dropped the table which is one of the important table in your database.
the table and its data still availble in "bin".
What Oracle does when you delete the table?
Oracle Simply rename table with differnt tablename start with "BINsomevalue".
Make sure you enable this recyclebin
SQL> show parameter recyclebin;
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
recyclebin                           string      on

Simply login to the scheama & fire command "show recyclebin;"


Here is demo for you.
-
SQL> conn scott
Enter password:
Connected.
SQL> desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> drop table emp1;
Table dropped.

SQL> desc emp1;
ERROR:
ORA-04043: object emp1 does not exist

SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP1             BIN$nChBttgnS/6uj2vZgTB9Ow==$0 TABLE        2012-09-07:17:58:13

SQL> flashback table emp1 to before drop;
Flashback complete.


SQL> desc emp1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                              NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)



This way you recovered your dropped table.

No comments:

Post a Comment