基于依赖UNDO技术的FLASHBACK特性有四种，分别是：
1.FLASHBACK TABLE (恢复行级数据)
2.FLASHBACK QUERY
3.FLASHBACK VERSION QUERY
4.FLASHBACK TRANSACTION QUERY
（1）FLASHBACK TABLE
--引用自Oracle官方文档
Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system. Also, Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table.
You cannot roll back a FLASHBACK TABLE statement. However, you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN before issuing a FLASHBACK TABLE clause.
By default, Oracle Database disables all enabled triggers defined on table during the Flashback Table operation and then reenables them after the Flashback Table operation is complete. Specify ENABLE TRIGGERS if you want to override this default behavior and keep the triggers enabled during the Flashback process.
This clause affects only those database triggers defined on table that are already enabled. To enable currently disabled triggers selectively, use the ALTER TABLE ... enable_disable_clause before you issue the FLASHBACK TABLE statement with the ENABLE TRIGGERS clause.
--创建测试表：
SQL> conn albert/albert
Connected.
SQL> create table Liangce as select * from dba_objects;
Table created.
SQL> select object_type,count(*) from liangce group by object_type;
OBJECT_TYPE COUNT(*)
------------------- ----------
EDITION 1
INDEX PARTITION 370
TABLE SUBPARTITION 32
CONSUMER GROUP 25
SEQUENCE 182
TABLE PARTITION 358
SCHEDULE 3
QUEUE 24
RULE 1
JAVA DATA 314
PROCEDURE 139
OPERATOR 23
LOB PARTITION 5
DESTINATION 2
WINDOW 9
SCHEDULER GROUP 4
DATABASE LINK 1
LOB 238
PACKAGE 785
PACKAGE BODY 748
LIBRARY 149
PROGRAM 19
RULE SET 15
CONTEXT 7
TYPE BODY 126
JAVA RESOURCE 864
TRIGGER 69
JOB CLASS 13
UNDEFINED 11
DIRECTORY 5
TABLE 1600
INDEX 1978
SYNONYM 30947
VIEW 4531
FUNCTION 131
JAVA CLASS 27024
JAVA SOURCE 2
INDEXTYPE 5
CLUSTER 10
TYPE 1448
RESOURCE PLAN 10
JOB 11
EVALUATION CONTEXT 11
43 rows selected.
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,
-----------------
20200425 13:22:02
开始“误操作”行为：
SQL> update liangce set owner='Travelskydba' WHERE OWNER <> 'SYS';
33643 rows updated.
SQL> commit;
Commit complete.
SQL> select owner,count(*) from liangce group by owner order by 2 desc;
OWNER COUNT(*)
------------------------------ ----------
SYS 38607
Travelskydba 33643
-----commit后发现为误操作行为-----
执行恢复操作：
SQL> alter table liangce enable row movement;
Table altered.
SQL> flashback table liangce to timestamp to_timestamp('20200425 13:22:02','yyyymmdd hh24:mi:ss');
--也可支持to_scn
Flashback complete.
SQL> select owner,count(*) from liangce group by owner order by 2 desc;
OWNER COUNT(*)
------------------------------ ----------
SYS 38607
PUBLIC 30932
OLAPSYS 721
SYSTEM 622
CTXSYS 389
WMSYS 333
RMAN 252
EXFSYS 192
OTSC 77
DBSNMP 57
APPUSER 17
B2C3U_XREZAGENT 10
OUTLN 10
ORACLE_OCM 8
LCA 6
APPQOSSYS 5
HJ 4
DEMO 3
TRAVELSKYDBA 2
LIANGCE 2
ALBERT 1
21 rows selected.
（2）FLASHBACK QUERY
--引用Oracle 官方文档--
To use Oracle Flashback Query, use a SELECT statement with an AS OF clause. Oracle Flashback Query retrieves data as it existed at an earlier time. The query explicitly references a past time through a time stamp or System Change Number (SCN). It returns committed data that was current at that point in time.
Uses of Oracle Flashback Query include:
Recovering lost data or undoing incorrect, committed changes.
For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
Comparing current data with the corresponding data at an earlier time.
For example, you can run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
Checking the state of transactional data at a particular time.
For example, you can verify the account balance of a certain day.
Selecting data that was valid at a particular time or at any time within a user-defined valid time period.
For example, you can find employees with valid employee information as of a particular timestamp or between a specified start and end time in the specified valid time period. (For more information, see Temporal Validity Support.)
Simplifying application design by removing the need to store some kinds of temporal data.
Oracle Flashback Query lets you retrieve past data directly from the database.
Applying packaged applications, such as report generation tools, to past data.
Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.
创建测试表，借助闪回查询功能进行数据恢复：
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> show user
USER is "ALBERT"
SQL> select to_char(sysdate,'yyyymmdd hh24:mi:ss') from dual;
TO_CHAR(SYSDATE,
-----------------
20200425 14:06:00
SQL> delete from t1;
72252 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
COUNT(*)
----------
0
SQL> select count(*) from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');
COUNT(*)
----------
72252
SQL> insert into t1 select * from t1 as of timestamp to_timestamp('20200425 14:06:00','yyyymmdd hh24:mi:ss');
72252 rows created.
SQL> commit;
Commit complete.
（3）FLASHBACK VERSION QUERY
--引用自Oracle官方文档--
Use Oracle Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A row version is created whenever a COMMIT statement is executed.
创建测试表并开始闪回操作：
SQL> select *from t2;
OWNER OBJECT_ID
------------------------------ ----------
ALBERT 777
TRAVELSKYDBA 20
SYS 46
SYS 28
SYS 29
SYS 3
SYS 25
SYS 41
SYS 54
9 rows selected.
SQL>
SQL>
SQL> delete from t2 where owner='ALBERT'; --事务1 版本1
1 row deleted.
SQL> delete from t2 where owner='TRAVELSKYDBA'; --事务1 版本1
1 row deleted.
SQL> update t2 set owner='TRAVELSKYDBA'; --事务2 版本2
7 rows updated.
SQL> commit;
Commit complete.
SQL> insert into t2 values('ALBERT',77); --事务3 版本3
1 row created.
SQL> commit;
Commit complete.
一张表经历了多次事务，我们可以利用闪回查询进行不同事务版本中的闪回切换：
SSQL> SELECT owner,
2 object_id,
3 versions_startscn,
4 to_char(versions_starttime, 'yyyymmdd hh24:mi:ss') versions_starttime,
5 versions_endscn,
6 to_char(versions_endtime, 'yyyymmdd hh24:mi:ss') versions_endtime,
7 versions_xid,
8 versions_operation
9 FROM t2 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('20200425 15:54:00', 'yyyymmdd hh24:mi:ss') AND TO_TIMESTAMP('20200425 15:58:40', 'yyyymmdd hh24:mi:ss')
10 order by 3;
OWNER OBJECT_ID VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION
------------------------------ ---------- ----------------- ------------------ --------------- ----------------- ---------------- ------------------
TRAVELSKYDBA 20 108825318 20200425 15:54:49 0A001700E59E1900 D --事务1 版本1：delete from t2 where owner='TRAVELSKYDBA';
ALBERT 777 108825318 20200425 15:54:49 0A001700E59E1900 D --事务1 版本1：delete from t2 where owner='ALBERT';
TRAVELSKYDBA 3 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 29 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 28 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 46 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 25 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 41 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
TRAVELSKYDBA 54 108825426 20200425 15:56:16 0A00060036771900 U --事务2 版本2：update t2 set owner='TRAVELSKYDBA';
ALBERT 77 108825465 20200425 15:56:56 0A000800649D1900 I --事务3 版本3：insert into t2 values('ALBERT',77);
TRAVELSKYDBA 20 108825318 20200425 15:54:49
SYS 46 108825426 20200425 15:56:16
SYS 54 108825426 20200425 15:56:16
SYS 29 108825426 20200425 15:56:16
SYS 3 108825426 20200425 15:56:16
SYS 25 108825426 20200425 15:56:16
SYS 41 108825426 20200425 15:56:16
ALBERT 777 108825318 20200425 15:54:49
SYS 28 108825426 20200425 15:56:16
19 rows selected
--current t2 table状态：
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 46
TRAVELSKYDBA 28
TRAVELSKYDBA 29
TRAVELSKYDBA 3
TRAVELSKYDBA 25
TRAVELSKYDBA 41
TRAVELSKYDBA 54
ALBERT 77
8 rows selected.
恢复T2表至最初状态（未发生过事务）
SQL> alter table t2 enable row movement;
Table altered.
SQL> flashback table t2 to scn 108825317; VERSIONS_ENDSCN为18
Flashback complete.
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
ALBERT 777
TRAVELSKYDBA 20
SYS 46
SYS 28
SYS 29
SYS 3
SYS 25
SYS 41
SYS 54
9 rows selected.
恢复至T2表至INSERT之前状态：
SQL> flashback table t2 to scn 108825426;
Flashback complete.
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 46
TRAVELSKYDBA 28
TRAVELSKYDBA 29
TRAVELSKYDBA 3
TRAVELSKYDBA 25
TRAVELSKYDBA 41
TRAVELSKYDBA 54
7 rows selected.
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 46
TRAVELSKYDBA 28
TRAVELSKYDBA 29
TRAVELSKYDBA 3
TRAVELSKYDBA 25
TRAVELSKYDBA 41
TRAVELSKYDBA 54
ALBERT 77
8 rows selected.
SQL> flashback table t2 to scn 108825465;
Flashback complete.
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 46
TRAVELSKYDBA 28
TRAVELSKYDBA 29
TRAVELSKYDBA 3
TRAVELSKYDBA 25
TRAVELSKYDBA 41
TRAVELSKYDBA 54
ALBERT 77
8 rows selected.
（4）Flashback Transaction Query
--引用自Oracle官方文档---
Use Oracle Flashback Transaction Query to retrieve metadata and historical data for a given transaction or for all transactions in a given time interval. Oracle Flashback Transaction Query queries the static data dictionary view FLASHBACK_TRANSACTION_QUERY, whose columns are described in Oracle Database Reference.
The column UNDO_SQL shows the SQL code that is the logical opposite of the DML operation performed by the transaction. You can usually use this code to reverse the logical steps taken during the transaction. However, there are cases where the UNDO_SQL code is not the exact opposite of the original transaction. For example, a UNDO_SQL INSERT operation might not insert a row back in a table at the same ROWID from which it was deleted.
闪回事务查询功能中，除了数据恢复之外，还有一项重要的功能是，提供给我们是谁修改了某个表的某行数据，例如，我们发现ALBERT中的T2表被进行了修改，但是不知道是谁修改了，可以利用如下查询进行操作：
SQL> conn p_liangce/p_liangce;
Connected.
SQL>
SQL>
SQL> select * from albert.t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
ALBERT 666
8 rows selected.
SQL> update albert.t2 set object_id=666666 where owner='ALBERT'
1 rows updated.
SQL> commit;
Commit complete.
LIANGCE这个用户对ALBERT 用户下表T2某行进行了UPDATE操作，我们可以用如下方式进行追踪与数据修改
此时ALBERT用户向DBA数据被修改，DBA可以利用闪回事务查询中的LOGON_USER进行修改
SQL> select * from t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
ALBERT 666666 --原来应为666
8 rows selected.
SQL>
SQL> select logon_user,
2 start_timestamp,
3 commit_timestamp,
4 operation,
5 table_name,
6 table_owner,
7 row_id,
8 undo_sql
9 from flashback_transaction_query
10 where table_name = 'T2'
11 AND table_OWNER = 'ALBERT'
12 and start_timestamp between
13 to_timestamp('20200425 16:00:00', 'yyyymmdd hh24:mi:ss') and
14 to_timestamp('20200425 16:30:00', 'yyyymmdd hh24:mi:ss')
15 ;
LOGON_USER START_TIMESTAMP COMMIT_TIMESTAMP OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAA delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAA';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAB delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAB';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAJ delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAJ';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAK delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAK';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAL delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAL';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAM delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAM';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAN delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAN';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAO delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAI insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','54');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAH insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','41');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAG insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','25');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAF insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','3');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAE insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','29');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAD insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','28');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAC insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','46');
P_LIANGCE 2020/4/25 16:27 2020/4/25 16:28: UPDATE T2 ALBERT AAAkGVAANAAD1AFAAO update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAC delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAC';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAD delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAD';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAE delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAE';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAF delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAF';
LOGON_USER START_TIMESTAMP COMMIT_TIMESTAMP OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
------------------------------ --------------- ---------------- -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAG delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAG';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAH delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAH';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: INSERT T2 ALBERT AAAkGVAANAAD1AFAAI delete from "ALBERT"."T2" where ROWID = 'AAAkGVAANAAD1AFAAI';
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAQ insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','54');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAP insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','41');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAO insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','25');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAN insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','3');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAM insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','29');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAL insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','28');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAK insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('SYS','46');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAB insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('TRAVELSKYDBA','20');
ALBERT 2020/4/25 16:05 2020/4/25 16:05: DELETE T2 ALBERT AAAkGVAANAAD1AFAAA insert into "ALBERT"."T2"("OWNER","OBJECT_ID") values ('ALBERT','777');
-数据恢复：
SQL> update "ALBERT"."T2" set "OBJECT_ID" = '666' where ROWID = 'AAAkGVAANAAD1AFAAO'; --执行UNDO SQL前请谨慎确认是否执行的是误操作之后的反向操作。
1 row updated.
SQL>
SQL>
SQL>
SQL> commit;
Commit complete.
SQL> select * from albert.t2;
OWNER OBJECT_ID
------------------------------ ----------
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
TRAVELSKYDBA 666
ALBERT 666
8 rows selected.
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200719-1-1024x490.png)
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200719-2-1024x293.png)
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200712-1-1024x75.png)
![](http://albertdba.com/"wp-content/uploads"/2020/07/20200712-2-1024x348.png)