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掉的表”有了更深的了解，不妨來實際操作一番吧！這里是億速云網站，更多相關內容可以進入相關頻道進行查詢，關注我們，繼續學習！