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.
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