對于DELETE的誤操作
當不小心用DELETE刪除了表中的一條或多條記錄的恢復

1,基于時間的閃查
閃查的查詢能力是受到undo表空間的大小和undo_retention的影響
以SCOTT.EMP為例子簡單示范下
SQL> SHOW USER
USER is "SCOTT"

SQL> SELECT COUNT(*) FROM EMP;

? COUNT(*)
----------
??????? 14

SQL> DELETE EMP WHERE ENAME='SCOTT';

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL> SELECT COUNT(*) FROM EMP;

? COUNT(*)
----------
??????? 13

SQL> SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-5/1440 WHERE ENAME='SCOTT';
??? (上一個5分鐘之內的,1440表示24*60,也可以根據自己定義的時間格式來寫具體的時間)

EMPNO ENAME????? JOB????????? MGR HIREDATE????????????? SAL? COMM???? DEPTNO
----- ---------- ---------- ----- ------------------- ----- ----- ----------
?7788 SCOTT????? ANALYST???? 7566 1987-04-19 00:00:00? 3000?????????????? 20

SQL> INSERT INTO EMP SELECT * FROM EMP AS OF TIMESTAMP SYSDATE-5/1440 WHERE ENAME='SCOTT';

1 row created.

SQL> SELECT COUNT(*) FROM EMP;

? COUNT(*)
----------
??????? 14

SQL> COMMIT;

Commit complete.

2,基于SCN的閃查(刪除的步驟就不重復了)
SQL> SHOW USER
USER is "SYS"

SQL> SELECT SCN,TIME_DP FROM (SELECT SCN,TIME_DP FROM SMON_SCN_TIME ORDER BY TIME_DP DESC) WHERE ROWNUM<5;

?????? SCN TIME_DP
---------- -------------------
??? 874277 2011-06-18 01:05:48
??? 874073 2011-06-18 01:00:21
??? 873920 2011-06-18 00:59:57
??? 873774 2011-06-18 00:54:27
??? (5分鐘一寫,可以根據時間查詢SCN來進行恢復.)
SQL> SELECT * FROM SCOTT.EMP AS OF SCN 873774 WHERE ENAME='SCOTT';

EMPNO ENAME????? JOB????????? MGR HIREDATE????????????? SAL? COMM???? DEPTNO
----- ---------- ---------- ----- ------------------- ----- ----- ----------
?7788 SCOTT????? ANALYST???? 7566 1987-04-19 00:00:00? 3000?????????????? 20

注：初學者，歡迎指點，不斷改進。doo


?

轉載于:https://blog.51cto.com/dongyin/591189

總結

以上是生活随笔為你收集整理的DELETE误操作_oracle的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。