Oracle數(shù)據(jù)庫在多版本數(shù)據(jù)管理的設(shè)計(jì)是非常優(yōu)秀的扮饶，數(shù)據(jù)的誤刪恢復(fù)非常簡單逗鸣。除了truncate以外枝嘶，drop table語句和所有DML語句都是可以輕松恢復(fù)帘饶。
恢復(fù)DROPED TABLE
Oracle和windows類似，也有個(gè)‘回收站’的概念群扶，查看回收站的方法很多及刻，例如user_recyclebin系統(tǒng)視圖是比較常用的镀裤，該視圖中的內(nèi)容包括（恢復(fù)操作所需要的）：
- 被刪除的表在回收站中叫什么名字（OBJECT_NAME）
- 被刪除的表原來叫什么名字（ORIGINAL_NAME）
- 表是什么時(shí)候被刪除的（DROPTIME）
desc user_recyclebin;
名稱 空值? 類型
-------------- -------- ------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER
- 示例：
先簡單創(chuàng)建一張測試表，然后刪除
SQL> conn test/test
Connected.
SQL> create table mytbl as select 1 as flag from dual;
Table created.
SQL> select * from mytbl;
FLAG
----------
1
SQL> drop table mytbl;
Table dropped.
然后可以在user_recyclebin中找到我們剛才刪除的表
SQL> col object_name for a30
SQL> col original_name for a30
SQL> col droptime for a30
SQL> set linesize 1000
SQL> select object_name,original_name,droptime from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ ------------------------------ ------------------------------
BIN$ksxWUEIJRM3gUADAhgIE7Q==$0 MYTBL 2019-09-17:23:21:47
可以使用OBJECT_NAME來查看表中的數(shù)據(jù)缴饭，OBJECT_NAME是由系統(tǒng)自動(dòng)生成的名字暑劝，需要用雙引號“”才能被識別為表名。
SQL> select * from "BIN$ksxWUEIJRM3gUADAhgIE7Q==$0";
FLAG
----------
1
接下來就可以用flashback table to before drop或flashback table to before drop rename to命令將表找回或重命名為新的表名
SQL> flashback table "BIN$ksxWUEIJRM3gUADAhgIE7Q==$0" to before drop;
Flashback complete.
SQL> select * from mytbl;
FLAG
----------
1
SQL> select * from user_recyclebin;
no rows selected
flashback table “XXX” to before drop rename to XXX命令可以將表命名為新的名字颗搂，而非ORIGINAL_NAME担猛。恢復(fù)之后丢氢，回收站中該對象就看不到了毁习。
由此我們可以看出，所謂的DROP TABLE表刪除本質(zhì)上僅僅是將表重命名為新的名字卖丸，而非物理清除纺且。如果想物理清除需要使用PURGE關(guān)鍵字。
SQL> drop table mytbl purge;
Table dropped.
SQL> select * from user_recyclebin;
no rows selected
當(dāng)然稍浆，被刪除的表不可能永遠(yuǎn)保留载碌，保留的時(shí)間和表空間的剩余空間有關(guān)，當(dāng)剩余表空間不足時(shí)衅枫，則回收站里的對象會被清理掉嫁艇，優(yōu)先清理最早放進(jìn)回收站的（DROPTIME最小的）。
恢復(fù)CHANGED ROWS
和被刪除的表類似弦撩，被DML語句修改的行步咪，也會在數(shù)據(jù)庫中保留一段時(shí)間，保留時(shí)間長短和UNDO的保留策略以及UNDO的實(shí)際使用情況有關(guān)益楼，稍后會更詳細(xì)講解猾漫。先來看看如何對已經(jīng)提交的DML語句影響的行恢復(fù)到語句執(zhí)行之前的狀態(tài)（未提交的直接執(zhí)行rollback即可）。
方法一：VERSIONS BETWEEN
- 示例：
模擬誤刪場景感凤，先查看一下EMP表中的數(shù)據(jù)悯周，然后刪除表中的一行并提交，
SQL> conn scott/tiger
Connected.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> delete from emp where empno = 7369;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from emp where empno = 7369;
no rows selected
利用VERSIONS子句查看被刪除的行相關(guān)的信息
SQL> select * from emp versions between scn minvalue and maxvalue where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
我們明明只刪了一行陪竿，為什么查詢的結(jié)果中有兩行呢禽翼？加上versions between scn minvalue and maxvalue關(guān)鍵字后，實(shí)際上是顯示了表上數(shù)據(jù)行所有變化的“流水賬”族跛，準(zhǔn)確的說闰挡，并非所有變化，而是UNDO保留時(shí)間窗口內(nèi)記錄的所有”流水賬“加上表在UNDO保留時(shí)間窗口的起始時(shí)間點(diǎn)的靜態(tài)版本礁哄。比如长酗，undo_retention參數(shù)設(shè)置成了3600，表示這個(gè)保留時(shí)間窗口為1小時(shí)（3600s）姐仅，那么versions子句所能看到的數(shù)據(jù)為：表在一小時(shí)前那個(gè)時(shí)間點(diǎn)的靜態(tài)版本+這一小時(shí)內(nèi)的所有的變化流水花枫。
流水記錄中的內(nèi)容除了包括數(shù)據(jù)行內(nèi)容本身以外，還包括這一行上發(fā)生的具體操作是什么（versions_operation輔助隱藏列掏膏，D表示delete劳翰，I表示insert，U表示update）馒疹，操作發(fā)生的時(shí)間是什么（導(dǎo)致該行發(fā)生變化的操作何時(shí)開始versions_startscn,該行再次發(fā)生變化的時(shí)間即該行不再是最新行版本的時(shí)間versions_endscn佳簸，SCN是數(shù)據(jù)庫隨時(shí)間單調(diào)遞增的系統(tǒng)變更號，可以理解為時(shí)間）
SQL> col versions_startscn for 99999999999999
SQL> col versions_endscn for 99999999999999
SQL> col versions_operation for a30
SQL> select versions_operation,versions_startscn,versions_endscn,t.empno
from emp versions between scn minvalue and maxvalue t;
VERSIONS_OPERATION VERSIONS_STARTSCN VERSIONS_ENDSCN EMPNO
-------------------- ----------------- --------------- ------------
D 145852760112 7369
145852760112 7369
7499
7521
7566
7654
7698
7782
7788
7839
7844
7876
7900
7902
7934
15 rows selected.
這樣我們就能清楚地區(qū)分出EMPNO=7369的兩行當(dāng)中颖变，versions_operation='D'的那一行是我們剛才刪除的那一行生均，而另一行的versions_operation is null表示該行為初始版本（UNDO保留最早時(shí)間點(diǎn)的數(shù)據(jù)版本），由于被delete之前腥刹，改行沒有其他操作马胧，實(shí)際上兩行的數(shù)據(jù)部分完全一致，恢復(fù)時(shí)只需要對查詢結(jié)果去重即可衔峰，或者添加versions_operation = 'D'條件
現(xiàn)在我們開始恢復(fù)：
SQL> insert into emp select * from emp versions between scn minvalue and maxvalue where versions_operation = 'D' and empno = 7369;
1 rows inserted.
commit;
方法二：FLASHBACK
- 原理簡單介紹：
Oracle FLASHBACK TABLE 功能同樣是利用了UNDO“記流水賬”的功能佩脊，但FLASHBACK TABLE TO BEFORE DROP不是，前面已經(jīng)說了垫卤，這是使用了“回收站”的原理威彰。回收站純粹是為了防誤刪而設(shè)計(jì)的穴肘，而UNDO除了誤刪恢復(fù)以外歇盼，還有其他更重要的意義——UNDO和事務(wù)的一致性和隔離性息息相關(guān)、事務(wù)的回滾（rollback命令或事務(wù)的異常終止）和一致性讀需要UNDO评抚。這里給大家簡單科普一下UNDO的大致作用豹缀，若當(dāng)前會話上的事務(wù)對數(shù)據(jù)進(jìn)行了修改，在執(zhí)行提交之前慨代，其他的會話不應(yīng)該看到當(dāng)前會話修改后的內(nèi)容（事務(wù)隔離性）耿眉，但數(shù)據(jù)又的確是修改了，只是沒有提交鱼响，因此需要UNDO來實(shí)現(xiàn)其他會話看到當(dāng)前會話修改之前的數(shù)據(jù)版本鸣剪，UNDO具有這種記流水賬的功能，因此具備提供時(shí)間窗口內(nèi)任意一個(gè)時(shí)間點(diǎn)的數(shù)據(jù)版本的能力丈积。
- 示例：
查看數(shù)據(jù)筐骇，然后刪除一行，刪除之前查看一下時(shí)間江滨，主要是為了記錄一個(gè)誤刪之前的時(shí)間點(diǎn)铛纬，后續(xù)恢復(fù)需要用到這個(gè)時(shí)間。這種方法相對于versions方法的局限性在于需要回憶起誤刪操作的大致時(shí)間范圍唬滑。
SQL> conn scott/tiger
Connected.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as cur_date from dual;
CUR_DATE
-------------------
2019-09-19 02:07:15
SQL> delete from emp where empno = 7369;
1 row deleted.
SQL> commit;
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as cur_date from dual;
CUR_DATE
-------------------
2019-09-19 02:08:29
SQL> select * from emp where empno = 7369;
no rows selected
使用as of timestamp子句查看指定時(shí)間點(diǎn)的數(shù)據(jù)版本
SQL> select * from emp as of timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> select * from emp as of timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss') where empno = 7369;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
然后開始恢復(fù)告唆，可以直接將as of timestamp語句(閃回查詢)查到的誤刪行insert回去棺弊，也可以用flashback table to timestamp語句進(jìn)行閃回操作。
SQL> flashback table emp to timestamp to_timestamp('2019-09-19 02:07:15','yyyy-mm-dd hh24:mi:ss');
Flashback complete.
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
兩種方法的區(qū)別和優(yōu)缺點(diǎn)總結(jié)：
versions方法受限于undo_retention參數(shù),時(shí)間窗口會隨著時(shí)間不段向后推進(jìn)擒悬，若undo_retention設(shè)置過小模她，則很快就沒法查到變化記錄，不過可以臨時(shí)調(diào)大參數(shù)來滿足查詢懂牧，優(yōu)點(diǎn)是可以查到所有行的修改操作記錄侈净，缺點(diǎn)是比較復(fù)雜，不太直觀僧凤。FLASHBACK方法則可以查到UNDO中實(shí)際存在的所有數(shù)據(jù)版本畜侦，和undo_rentention參數(shù)無關(guān)，UNDO保留的機(jī)制和回收站類似躯保，取決于UNDO表空間的剩余大小旋膳，還受到相關(guān)參數(shù)的影響，flashback table方法的優(yōu)點(diǎn)是便捷途事、直觀溺忧，缺點(diǎn)是需要找到相對準(zhǔn)確的時(shí)間點(diǎn)。
