误删oracle表，恢复表结构及数据方法

1、本人是通过drop的方式误删了所有正式库的所有表，瞬间崩溃了；

2、oracle是10g版本，晚上说只有该版本有回收站的概念，这个未去深究过；

3、去回收表中查看下被你误删的表是否还存在？

4、如果你的表空间建立的足够大，因为oracle的回收机制类似于内存，如果存储过小，它将不会将你误删的表回收到回收站中；

具体操作可以查看连接：http://www.2cto.com/database/201110/109490.html

在10g以前是没有这个功能的，删除后就没有了，只能从备份恢复。

 

＃将执行的操作记录在文件中，就是屏幕输出的内容

SQL>spool '/home/oracle/drop.txt'

 

 

＃＃删除emp表

SQL> drop table emp ;

 

Table dropped.

 

＃现在查看emp已经不存在了

SQL> select * from emp ;

select * from emp

              *

ERROR at line 1:

ORA-00942: table or view does not exist

 

＃查看回收站的内容

SQL> show recyclebin

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME         

---------------- ------------------------------ ------------ -------------------

EMP              BIN$sGRMOtFvhCPgQKjABwAOsw==$0 TABLE        2011-10-29:06:20:34

 

＃查看用户回收站的内容

SQL> select * from user_recyclebin

  2  ;

 

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION      

------------------------------ -------------------------------- ---------      

TYPE                      TS_NAME                        CREATETIME            

------------------------- ------------------------------ -------------------   

DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN        

------------------- ---------- -------------------------------- --- ---        

   RELATED BASE_OBJECT PURGE_OBJECT      SPACE                                 

---------- ----------- ------------ ----------                                 

BIN$sGRMOtFvhCPgQKjABwAOsw==$0 EMP                              DROP           

TABLE                     USERS                          2005-06-30:19:47:57   

2011-10-29:06:20:34     517667                                  YES YES        

     51148       51148        51148          8                                  

                                                                               

 

OBJECT_NAME                    ORIGINAL_NAME                    OPERATION      

------------------------------ -------------------------------- ---------      

TYPE                      TS_NAME                        CREATETIME            

------------------------- ------------------------------ -------------------   

DROPTIME               DROPSCN PARTITION_NAME                   CAN CAN        

------------------- ---------- -------------------------------- --- ---        

   RELATED BASE_OBJECT PURGE_OBJECT      SPACE                                 

---------- ----------- ------------ ----------                                 

BIN$sGRMOtFuhCPgQKjABwAOsw==$0 PK_EMP                           DROP           

INDEX                     USERS                          2005-06-30:19:47:57   

2011-10-29:06:20:34     517663                                  NO  YES        

     51148       51148        51149          8                                 

                                                                               

将回收站的表恢复出来 www.2cto.com

SQL> flashback table emp to before drop ;

 

Flashback complete.

 

再看回收站就没有东西了

SQL> show recyclebin

 

emp现在又可以用了

SQL> select * from emp where sal > 3000;

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     

---------- ---------- --------- ---------- --------- ---------- ----------     

    DEPTNO                                                                      

----------                                                                     

      7839 KING       PRESIDENT            17-NOV-81       5000                

        10                                                                      

                                                                               

 

SQL> spool off

[root@bogon oracle]#

 

相关知识点：

1、从回收站恢复时重命名表

SQL> flashback table t2 to before drop rename to t4 ;

 

2、删除回收站指定的表

SQL> purge table t4;

 

3、清空回收站

SQL> purge recyclebin

 

4、删除表时不经过回收站直接删除

 

SQL> drop table t3 purge ;