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 ope免费云主机域名ned

找到丢失的表然后执行导出导入到目标库就可以了
参考文档
How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (文档 ID 223543.1)
到此，相信大家对“SQL怎么恢复drop掉的表”有了更深的了解，不妨来实际操作一番吧！这里是百云网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！

相关推荐: ASM中怎么重命名kgroup

本篇文章给大家分享的是有关ASM中怎么重命名kgroup，小编觉得挺实用的，因此分享给大家学习，希望大家阅读完这篇文章后可以有所收获，话不多说，跟着小编一起来看看吧。ASM 11.2.0.1 版本开始增加了diskgroup重命名的新功能，通过renamedg…