分为闪回版本查询和闪回事务查询两步，可针对事务进行闪回，比9i更进一步。
SQL> create table test1 as select 1 a, 2 b from dual;
Table created
SQL> insert into test1 values(2,2);
1 row inserted
SQL> insert into test1 values(3,3);
1 row inserted
SQL> insert into test1 values(4,4);
1 row inserted
SQL> insert into test1 values(5,5);
1 row inserted
SQL> update test1 set a = 55 where a = 5;
1 row updated
SQL> delete from test1 where a = 4;
1 row deleted
SQL> commit;
Commit complete
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation, a, b from test1 versions between timestamp minvalue and maxvalue;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION A B
------------------ ---------------- ---------------- ------------------ -- --
1 2
23-8月 0200200012020000 I 55 5
23-8月 0200200012020000 I 3 3
23-8月 0200200012020000 I 2 2
SQL> desc flashback_transaction_query;
Name Type Nullable Default Comments
---------------- -------------- -------- ------- -----------------------------------------
XID RAW(8) Y Transaction identifier
START_SCN NUMBER Y Transaction start SCN
START_TIMESTAMP DATE Y Transaction start timestamp
COMMIT_SCN NUMBER Y Transaction commit SCN
COMMIT_TIMESTAMP DATE Y Transaction commit timestamp
LOGON_USER VARCHAR2(30) Y Logon user for transaction
UNDO_CHANGE# NUMBER Y 1-based undo change number
OPERATION VARCHAR2(32) Y forward operation for this undo
TABLE_NAME VARCHAR2(256) Y table name to which this undo applies
TABLE_OWNER VARCHAR2(32) Y owner of table to which this undo applies
ROW_ID VARCHAR2(19) Y rowid to which this undo applies
UNDO_SQL VARCHAR2(4000) Y SQL corresponding to this undo
SQL> select * from flashback_transaction_query where xid = '0200200012020000';
XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL
---------------- --------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- --------------------------------------------------------------------------------
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 1 DELETE TEST1 TEST AAAM9fAAGAAAAONAAC insert into "TEST"."TEST1"("A","B") values ('4','4');
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 2 UPDATE TEST1 TEST AAAM9fAAGAAAAONAAD update "TEST"."TEST1" set "A" = '5' where ROWID = 'AAAM9fAAGAAAAONAAD';
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 3 INSERT TEST1 TEST AAAM9fAAGAAAAONAAD delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAD';
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 4 INSERT TEST1 TEST AAAM9fAAGAAAAONAAC delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAC';
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 5 INSERT TEST1 TEST AAAM9fAAGAAAAONAAB delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAB';
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 6 INSERT TEST1 TEST AAAM9fAAGAAAAONAAA delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAA';
0200200012020000 1411203 2007-08-23 7:59 1411268 2007-08-23 8:00: TEST 7 BEGIN
7 rows selected
SQL> desc test1;
Name Type Nullable Default Comments
---- ------ -------- ------- --------
A NUMBER Y
B NUMBER Y
SQL> begin
2 for i in 1..10 loop
3 insert into test1 values(i+10,i+100);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> delete from test1 where rownum < 4;
3 rows deleted
SQL> commit;
Commit complete
SQL> update test1 set a = 111 where rownum < 4;
3 rows updated
SQL> commi;t
2
SQL> commit;
Commit complete
SQL> select * from test1;
A B
-- --
11 5
11 10
11 10
13 10
14 10
15 10
16 10
17 10
18 10
19 10
20 11
11 rows selected
SQL> select * from test1 versions between timestamp minvalue and maxvalue;
A B
-- --
1 2
1 2
11 10
11 10
11 5
3 3
2 2
20 11
19 10
18 10
17 10
16 10
15 10
14 10
13 10
12 10
11 10
55 5
3 3
2 2
20 rows selected
SQL> select a,b,versions_xid, versions_name from test1 versions between timestamp minvalue and maxvalue;
select a,b,versions_xid, versions_name from test1 versions between timestamp minvalue and maxvalue
ORA-00904: "VERSIONS_NAME": 标识符无效
SQL> select a,b,versions_xid, versions_operation from test1 versions between timestamp minvalue and maxvalue;
A B VERSIONS_XID VERSIONS_OPERATION
-- -- ---------------- ------------------
1 2 0800240011020000 D
1 2
11 10 030024000D020000 U
11 10 030024000D020000 U
11 5 030024000D020000 U
3 3 0800240011020000 D
2 2 0800240011020000 D
20 11 06001A0011020000 I
19 10 06001A0011020000 I
18 10 06001A0011020000 I
17 10 06001A0011020000 I
16 10 06001A0011020000 I
15 10 06001A0011020000 I
14 10 06001A0011020000 I
13 10 06001A0011020000 I
12 10 06001A0011020000 I
11 10 06001A0011020000 I
55 5 0200200012020000 I
3 3 0200200012020000 I
2 2 0200200012020000 I
20 rows selected
SQL> select a,b,versions_xid, versions_operation, versions_starttime, versions_endtime from test1 versions between timestamp minvalue and maxvalue;
A B VERSIONS_XID VERSIONS_OPERATION VERSIONS_STARTTIME VERSIONS_ENDTIME
-- -- ---------------- ------------------ ------------------ ----------------
1 2 0800240011020000 D 23-8月
1 2 23-8月
11 10 030024000D020000 U 23-8月
11 10 030024000D020000 U 23-8月
11 5 030024000D020000 U 23-8月
3 3 0800240011020000 D 23-8月
2 2 0800240011020000 D 23-8月
20 11 06001A0011020000 I 23-8月
19 10 06001A0011020000 I 23-8月
18 10 06001A0011020000 I 23-8月
17 10 06001A0011020000 I 23-8月
16 10 06001A0011020000 I 23-8月
15 10 06001A0011020000 I 23-8月
14 10 06001A0011020000 I 23-8月
13 10 06001A0011020000 I 23-8月
12 10 06001A0011020000 I 23-8月 23-8月
11 10 06001A0011020000 I 23-8月 23-8月
55 5 0200200012020000 I 23-8月 23-8月
3 3 0200200012020000 I 23-8月 23-8月
2 2 0200200012020000 I 23-8月 23-8月
20 rows selected
SQL> select a,b,versions_xid, versions_operation, to_date(versions_starttime, 'yyyy-mm-dd'), versions_endtime from test1 versions between timestamp minvalue and maxvalue;
select a,b,versions_xid, versions_operation, to_date(versions_starttime, 'yyyy-mm-dd'), versions_endtime from test1 versions between timestamp minvalue and maxvalue
ORA-01861: 文字与格式字符串不匹配
SQL> select a,b,versions_xid, versions_operation, versions_starttime, versions_endtime from test1 versions between timestamp minvalue and maxvalue;
A B VERSIONS_XID VERSIONS_OPERATION VERSIONS_STARTTIME VERSIONS_ENDTIME
-- -- ---------------- ------------------ ------------------ ----------------
1 2 0800240011020000 D 23-8月
1 2 23-8月
11 10 030024000D020000 U 23-8月
11 10 030024000D020000 U 23-8月
11 5 030024000D020000 U 23-8月
3 3 0800240011020000 D 23-8月
2 2 0800240011020000 D 23-8月
20 11 06001A0011020000 I 23-8月
19 10 06001A0011020000 I 23-8月
18 10 06001A0011020000 I 23-8月
17 10 06001A0011020000 I 23-8月
16 10 06001A0011020000 I 23-8月
15 10 06001A0011020000 I 23-8月
14 10 06001A0011020000 I 23-8月
13 10 06001A0011020000 I 23-8月
12 10 06001A0011020000 I 23-8月 23-8月
11 10 06001A0011020000 I 23-8月 23-8月
55 5 0200200012020000 I 23-8月 23-8月
3 3 0200200012020000 I 23-8月 23-8月
2 2 0200200012020000 I 23-8月 23-8月
20 rows selected
SQL> select undo_sql from flashback_transaction_query where xid = '0800240011020000';
UNDO_SQL
--------------------------------------------------------------------------------
insert into "TEST"."TEST1"("A","B") values ('3','3');
insert into "TEST"."TEST1"("A","B") values ('2','2');
insert into "TEST"."TEST1"("A","B") values ('1','2');
SQL> select undo_sql from flashback_transaction_query where xid = '030024000D020000';
UNDO_SQL
--------------------------------------------------------------------------------
update "TEST"."TEST1" set "A" = '12' where ROWID = 'AAAM9fAAGAAAAONAAF';
update "TEST"."TEST1" set "A" = '11' where ROWID = 'AAAM9fAAGAAAAONAAE';
update "TEST"."TEST1" set "A" = '55' where ROWID = 'AAAM9fAAGAAAAONAAD';
SQL> select undo_sql from flashback_transaction_query where xid = '06001A0011020000';
UNDO_SQL
--------------------------------------------------------------------------------
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAN';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAM';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAL';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAK';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAJ';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAI';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAH';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAG';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAF';
delete from "TEST"."TEST1" where ROWID = 'AAAM9fAAGAAAAONAAE';
11 rows selected
相关推荐
### Oracle闪回功能详解与数据恢复实践 #### 引言 在数据库管理中，数据丢失是一种常见的风险，可能由各种原因造成，如误操作、硬件故障或软件错误等。为应对这种挑战，Oracle数据库自第10g版本起引入了强大的闪回...
本教程将深入探讨如何利用Oracle闪回功能来恢复偶然丢失的数据。 首先，我们需要了解Oracle闪回功能的基础概念。闪回功能包括闪回查询（Flashback Query）、闪回表（Flashback Table）、闪回事务（Flashback ...
下面将详细探讨Oracle闪回功能及其在数据恢复中的应用。 1. **闪回查询** (Flashback Query) 闪回查询允许用户查看数据库在特定时间点的状态，就像时光倒流一样。通过使用`SELECT AS OF TIMESTAMP`或`SELECT AS OF...
标题和描述中反复提及的“Oracle闪回”（Flashback）功能是Oracle数据库管理系统中一项极为重要的特性，它为数据库管理员提供了恢复数据到特定时间点的能力，而无需进行完全的数据库恢复过程。以下是对Oracle闪回...
通过本文对Oracle闪回技术的介绍，我们可以了解到如何设置和管理闪回恢复区，以及如何启用和禁用闪回数据库功能。这些步骤对于保障数据的安全性和提高数据库恢复能力具有重要意义。同时，我们也探讨了闪回恢复区中的...
本文旨在详细介绍Oracle闪回技术的各种应用场景与操作步骤，帮助读者理解并掌握如何利用Oracle闪回功能有效地进行数据恢复。Oracle闪回技术是一项强大的特性，能够在无需借助传统的备份和恢复流程的情况下，帮助用户...
在使用Oracle闪回功能前，需要满足以下条件： 1. **权限检查**：确保用户拥有对`dbms_flashback`包的执行权限。 2. **自动回滚段管理**：在`init.ora`文件中设置`UNDO_MANAGEMENT=AUTO`，启用自动管理的回滚段。...
Oracle 闪回功能是Oracle数据库提供的一种强大的恢复机制，它允许数据库管理员快速恢复数据库或部分数据库到一个先前的状态，以应对逻辑错误或误操作。在Oracle 10g中，闪回技术包括了多种级别的闪回，如数据库级别...
Oracle闪回技术是Oracle强大数据库备份恢复机制的一部分，在数据库发生逻辑错误的时候，闪回技术能提供快速且最小损失的恢复。闪回技术旨在快速恢复逻辑错误，对于物理损坏或是介质丢失的错误，闪回技术就回天乏术了...
Oracle 闪回技术是Oracle数据库提供的一种强大的数据恢复机制，它允许用户恢复到数据库的某个历史状态，而无需依赖传统的备份和恢复过程。这一技术基于Oracle的多版本并发控制（MVCC）机制，使得数据库能够记录和...
### Oracle闪回功能详解 #### 一、Oracle闪回概述 在Oracle数据库中，闪回功能是一组强大的特性集合，允许数据库管理员或用户在发生数据丢失或错误更改时，能够恢复到之前的某个状态。这些特性包括但不限于闪回表...
Oracle闪回数据库是一种高效的数据恢复技术，它允许管理员迅速恢复数据库至某一特定时间点，而无需执行完整的数据库恢复过程。这种技术显著提高了数据恢复的速度和便捷性，特别适合处理逻辑错误，例如用户误操作导致...
首先，我们来理解一下Oracle闪回技术的几个主要组件： 1. **闪回查询（Flashback Query）**：这是最基本的功能，可以让用户查看表或视图在特定时间点的状态。通过使用`AS OF TIMESTAMP`或`AS OF SCN`子句，我们可以...
在Oracle数据库管理中，**闪回删除的表**是一项非常实用的功能，它允许数据库管理员（DBA）恢复被误删的表及其所有数据。这一特性最早出现在Oracle 9i版本中，但在Oracle Database 10g中得到了进一步的增强和完善。 ...
为了使 Oracle 数据库从任何逻辑误操作中迅速地恢复...进行了全面扩展，提供了闪回数据库、闪回删除、闪回表、闪回事物及闪回版本查询等功能，本 文将重点说闪回删除、闪回表的使用。（本文使用oracle 版本10.2.0.3.0）
这一特性是Oracle数据库系统的核心优势之一，尤其在数据安全性要求极高的环境中，回闪功能显得尤为重要。 Oracle数据库的回闪机制主要基于两个核心技术：Redo Log（重做日志）和Undo Segment（回滚段）。Redo Log...