oracle數(shù)據(jù)庫中表誤操作恢復(fù)（delete、truncate、drop）
背景：
關(guān)于oracle數(shù)據(jù)庫中刪除操作：delete、drop、truncate 。經(jīng)常有技術(shù)人員誤操作將數(shù)據(jù)刪除。下面分別介紹此三種操作誤刪除后的恢復(fù)。例舉方案：delete 刪除表中特定數(shù)據(jù)。
語法：delete from tablename where …… --------基本sql語法，不過多描述。
操作演練
SYS@PROD1>conn scott/tiger
Connected.
SCOTT@PROD1>create table emp_tmp as select * from emp;
SCOTT@PROD1>select * from emp_tmp;
可查看表中數(shù)據(jù)。
SCOTT@PROD1>delete from emp_tmp where deptno=7788;
SCOTT@PROD1>delete from emp_tmp where empno=7788;
1 row deleted.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
13
解決方案：執(zhí)行rollback；
SCOTT@PROD1>rollback;
Rollback complete.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
14
SCOTT@PROD1>
*************************你懂的**************************************
下面進(jìn)入正題：使用drop或者truncate刪除了表中的數(shù)據(jù)怎么恢復(fù)？先說drop 因?yàn)橄啾萾runcate而言，drop某張表后立即恢復(fù)的話相對簡單（非drop purge）
(drop某張表，例如：drop table emp;此操作將不會立即將emp表的結(jié)構(gòu)及數(shù)據(jù)全部清除。只是會給emp表打個標(biāo)記，此標(biāo)記代表著emp已經(jīng)被刪除。除非通過特殊的手段查看---回收站。)
drop某張表之后不會立即將表數(shù)據(jù)及結(jié)構(gòu)清除，只是放入了回收站。進(jìn)入回收站將表取出即可。
操作演練：
SCOTT@PROD1>drop table emp_tmp;
Table dropped.
SCOTT@PROD1>select * from emp_tmp;
select * from emp_tmp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@PROD1>show recyclebin;
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP_TMP BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0 TABLE 2019-08-30:12:48:17
T BIN$kSPSWOVzEbrgUwsCAMDsdQ==$0 TABLE 2019-08-28:10:05:22
TEST2 BIN$kRECU/oVCtPgUwsCAMAMrg==$0 TABLE 2019-08-27:10:27:08
查看回收站恢復(fù)操作：方法一：
SCOTT@PROD1>select * from "BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0";
SCOTT@PROD1>create table emp_tmp as select * from "BIN$kU9UpPC2IRjgUwsCAMAzDQ==$0";
Table created.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
14
使用回收站中表直接恢復(fù)方法二：使用flashback
SCOTT@PROD1>drop table emp_tmp;
Table dropped.
SCOTT@PROD1>select count(*) from emp_tmp;
select count(*) from emp_tmp
ERROR at line 1:
ORA-00942: table or view does not exist
SCOTT@PROD1>flashback table emp_tmp to before drop;
Flashback complete.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
14
flashback表下面是重點(diǎn)中的重點(diǎn)；truncate table之后的恢復(fù)。（TRUNCATE不會逐個清除用戶數(shù)據(jù)塊上的數(shù)據(jù)，而僅僅重置數(shù)據(jù)字典和元數(shù)據(jù)塊上的元數(shù)據(jù)(如存儲段頭和擴(kuò)展段圖)。也就是說，此時(shí)，其基本數(shù)據(jù)并未被破壞，而是被系統(tǒng)回收、等待被重新分配————因此，要恢復(fù)被TRUNCATE的數(shù)據(jù)，需要及時(shí)備份其所在的數(shù)據(jù)文件）
模擬環(huán)境:
1^: SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
56
SCOTT@PROD1>truncate table emp_tmp;
Table truncated.
SCOTT@PROD1>select count(*) from emp_tmp;
COUNT(*)
----------
0
準(zhǔn)備環(huán)境
2^:建立存儲過程(執(zhí)行配套sql 語句 )
鏈接：
https://pan.baidu.com/s/1Ka90PGwytCR0JL5e139j8g
提取碼：kpv2
[oracle@edbjr2p1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 30 15:58:55 2019
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@PROD1>@FY_Recover_Data.pck
Enter value for files: ------------（此處直接回車即可）
old 30: -- 1. Temp Restore and Recover tablespace & files ---
new 30: -- 1. Temp Restore and Recover tablespace ---
Package created.
Package body created.
SYS@PROD1>
存儲過程建立
3^:執(zhí)行語句恢復(fù)表。
exec fy_recover_data.recover_truncated_table('SCOTT','EMP_TMP');
恢復(fù)會在原表的基礎(chǔ)上加 $$ 符號
如果表數(shù)據(jù)量過大，時(shí)間將較長。
SCOTT@PROD1>conn / as sysdba
Connected.
SYS@PROD1>exec fy_recover_data.recover_truncated_table('SCOTT','EMP_TMP');
PL/SQL procedure successfully completed.
SYS@PROD1>insert into emp_tmp select * from emp_tmp$$;
insert into emp_tmp select * from emp_tmp$$ *
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@PROD1>conn scott/tiger
Connected.
SCOTT@PROD1>insert into emp_tmp select * from emp_tmp$$;
56 rows created.
SCOTT@PROD1>
恢復(fù)表
4^:刪除臨時(shí)表空間即可------（執(zhí)行存儲過程的時(shí)候?qū)a(chǎn)生兩個臨時(shí)表空間）
SCOTT@PROD1>conn / as sysdba
Connected.
SYS@PROD1>select tablespace_name from user_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE
FY_REC_DATA
FY_RST_DATA
8 rows selected.
SYS@PROD1>drop tablespace FY_REC_DATA including contents and datafiles;
Tablespace dropped.
SYS@PROD1>drop tablespace FY_RST_DATA including contents and datafiles;
Tablespace dropped.
SYS@PROD1>
下一篇：Oracle數(shù)據(jù)庫備份恢復(fù)策略：應(yīng)對災(zāi)難的完美方案！