SQL怎么恢復(fù)drop掉的表

本篇內(nèi)容主要講解“SQL怎么恢復(fù)drop掉的表”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“SQL怎么恢復(fù)drop掉的表”吧!

如果誤刪數(shù)據(jù)，在10g或更高版本，可用使用回收站恢復(fù)drop掉的表。
首先確?；厥照驹谑褂弥?br/>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


從其他介質(zhì)恢復(fù)刪除的表
1.如果有在數(shù)據(jù)變更前的備份（exp，expdp，duplicate，rman備份），可用利用這個(gè)備份恢復(fù)數(shù)據(jù)庫(kù)或者表空間等等，找到變更前的表，然后轉(zhuǎn)入到數(shù)據(jù)庫(kù)中。
2.使用rman的until scn，seq，time等參數(shù)恢復(fù)數(shù)據(jù)庫(kù)或表空間到另外一個(gè)環(huán)境，找到更改前的表然后轉(zhuǎn)入正式庫(kù)
恢復(fù)數(shù)據(jù)庫(kù)
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到指定時(shí)間點(diǎn)
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


找到丟失的表然后執(zhí)行導(dǎo)出導(dǎo)入到目標(biāo)庫(kù)就可以了
參考文檔
How to Recover From a DROP / TRUNCATE / DELETE TABLE with RMAN (文檔 ID 223543.1)

到此，相信大家對(duì)“SQL怎么恢復(fù)drop掉的表”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！