watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=

          subject         part

stay Oracle in ,DELETE A piece of data and submitted , How to find it ?


     
          Answer section          



stay Oracle Flashback technology can be used to retrieve deleted and submitted data . Of course , In addition to flashback technology, you can also use LogMiner( You can easily get Redo The log file contains the specific contents of the archive log file ) Log mining , Find out the undo SQL And the execution will bring back DELETE Statement delete data .

The following example is based on the flashback principle to retrieve data :

 1SQL> SET TIMING ON;
 2SQL> SET SERVEROUTPUT ON;
 3SQL> SET SQLBLANKLINES ON;
 4SQL> SET LINESIZE 800
 5SQL> SET TIME ON;
 611:40:54 SQL> SELECT * FROM OLD_T;
 7        ID NAME
 8---------- --------------------
 9         1 LI
10 Elapsed time :  00: 00: 00.02
1111:41:05 SQL> UPDATE OLD_T SET NAME='LIH' WHERE ID=1;
12 Updated  1  That's ok .
13 Elapsed time :  00: 00: 00.01
1411:41:16 SQL> commit;
15 Submit completed .
16 Elapsed time :  00: 00: 00.03
1711:41:20 SQL> UPDATE OLD_T SET NAME='LIHU' WHERE ID=1;
18 Updated  1  That's ok .
19 Elapsed time :  00: 00: 00.01
2011:41:31 SQL> commit;
21 Submit completed .
22 Elapsed time :  00: 00: 00.01
2311:41:34 SQL> UPDATE OLD_T SET NAME='LIHUA' WHERE ID=1;
24 Updated  1  That's ok .
25 Elapsed time :  00: 00: 00.01
2611:41:39 SQL> COMMIT;
27 Submit completed .
28 Elapsed time :  00: 00: 00.01
2911:41:43 SQL> UPDATE OLD_T SET NAME='LIHUAR' WHERE ID=1;
30 Updated  1  That's ok .
31 Elapsed time :  00: 00: 00.02
3211:42:24 SQL> COMMIT;
33 Submit completed .
34 Elapsed time :  00: 00: 00.01
3511:42:26 SQL> SELECT * FROM OLD_T;
36        ID NAME
37---------- --------------------
38         1 LIHUAR
39 Elapsed time :  00: 00: 00.02
40------ Inquire about 5 Minutes ago
4111:45:53 SQL> SELECT * FROM OLD_T AS  OF TIMESTAMP SYSDATE-5/24/60 WHERE ID=1;
42        ID NAME
43---------- --------------------
44         1 LI
45 Elapsed time :  00: 00: 00.02
46------ Query the first transaction commit , The second transaction has not yet committed data
4711:55:13 SQL> SELECT * FROM OLD_T AS  OF TIMESTAMP TO_TIMESTAMP('2016-04-14 11:41:22','YYYY-MM-DD HH24:MI:SS') WHERE ID=1;
48        ID NAME
49---------- --------------------
50         1 LIH
51 Elapsed time :  00: 00: 00.02
52------ Query the second transaction commit , The third transaction has not yet committed data
5312:23:33 SQL> SELECT * FROM OLD_T AS  OF TIMESTAMP TO_TIMESTAMP('2016-04-14 11:41:38','YYYY-MM-DD HH24:MI:SS') WHERE ID=1;
54        ID NAME
55---------- --------------------
56         1 LIHU
57 Elapsed time :  00: 00: 00.01
     

You can see the submitted data , With flashback, you can still find the previous data , So as to recover to the previous data state . Of course , In addition to flashback technology, you can also use LogMiner( You can easily get Redo The log file contains the specific contents of the archive log file .) Do log mining to find out what it undoes SQL Statement execution can retrieve DELETE And submitted data .