SQL怎么恢复drop掉的表

本篇内容主要讲解“SQL怎么恢复drop掉的表”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“SQL怎么恢复drop掉的表”吧!

如果误删数据，在10g或更高版本，可用使用回收站恢复drop掉的表。
首先确保回收站在使用中
SQL> show parameter recycle


NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
buffer_pool_recycle     string
db_recycle_cache_size     big integer 0
recyclebin     string on


确保recyclebin中有drop掉的表


SQL> select owner,original_name from dba_recyclebin where original_name='BACKUPTEST';


OWNER       ORIGINAL_NAME
------------------------------ --------------------------------
SCOTT       BACKUPTEST


闪回表
SQL> flashback table scott.backuptest to before drop;


Flashback complete.


SQL> select * from scott.backuptest;


A
----------
1


从其他介质恢复删除的表
1.如果有在数据变更前的备份（exp，expdp，duplicate，rman备份），可用利用这个备份恢复数据库或者表空间等等，找到变更前的表，然后转入到数据库中。
2.使用rman的until scn，seq，time等参数恢复数据库或表空间到另外一个环境，找到更改前的表然后转入正式库
恢复数据库
RMAN> restore database ;         


Starting restore at 2017-07-30 14:44:56
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK


channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/test/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/test/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/1csal8ic_1_1
channel ORA_DISK_1: piece handle=/home/oracle/1csal8ic_1_1 tag=TAG20170730T131244
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 2017-07-30 14:45:12


recover到指定时间点
RMAN> recover database until time '2017-07-30 14:00:00';


Starting recover at 2017-07-30 14:51:57
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u01/app/oracle/oradata/test/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/test/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/test/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/1fsal9aj_1_1
channel ORA_DISK_1: piece handle=/home/oracle/1fsal9aj_1_1 tag=TAG20170730T132538
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01


starting media recovery
media recovery complete, elapsed time: 00:00:00


Finished recover at 2017-07-30 14:51:58


RMAN> alter database open resetlogs;


database opened


找到丢失的表然后执行导出导入到目标库就可以了
参考文档
How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (文档 ID 223543.1)

到此，相信大家对“SQL怎么恢复drop掉的表”有了更深的了解，不妨来实际操作一番吧！这里是亿速云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！