分为闪回版本查询和闪回事务查询两步,可针对事务进行闪回,比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闪回功能恢复偶然丢失的数据
用Oracle闪回功能恢复偶然丢失的数据
为了使 Oracle 数据库从任何逻辑误操作中迅速地恢复...进行了全面扩展,提供了闪回数据库、闪回删除、闪回表、闪回事物及闪回版本查询等功能,本 文将重点说闪回删除、闪回表的使用。(本文使用oracle 版本10.2.0.3.0)
Oracle闪回技术详解,这里整理了4种闪回技术,对Oracle 闪回技术做一个整理总结。 概述: 闪回技术是Oracle强大数据库备份恢复机制的一部分,在数据库发生逻辑错误的时候,闪回技术能提供快速且最小损失的恢复...
Oracle RAC环境下开启FLASHBACK闪回功能.pdf
Oracle的闪回技术提供了一组功能,可以访问过去某一时间的数据并从人为错误中恢复。闪回技术是Oracle 数据库独有的,支持任何级别的恢复,包括行、事务、表和数据库范围。使用闪回特性,您可以查询以前的数据版本,...
10g 回闪试验文档, 可以体验10g的回收功能。
Oracle10闪回功能实例讲解[收集].pdf
oracle数据库FLASHBACK系列功能介绍
利用oracle10g的新特性flashback闪回功能快速恢复oracle中被删除的表
如何在ORACLE9I下利用闪回功能恢复误删数据.pdf
一、Oracle闪回概述 闪回级别 闪回场景 闪回技术 对象依赖 影响数据 数据库 表截断、逻辑错误、其他多表意外事件 闪回DATABASE 闪回日志、undo 是 DROP 删除表 闪回DROP 回收站(recyclebin) 是 表 更新、...
在Oracle10g中,引入了一个回收站(Recycle Bin)的概念。...常常看到开发人员误删除表,我们可以充分利用10g的闪回(FLASHBACK)功能来避免类似的人为操作。不能对已经放到回收站(Recycle Bin)中的对象执行DDL/DML语句。
闪回技术是Oracle强大数据库备份恢复机制的一部分,在数据库发生逻辑错误的时候,闪回技术能提供快速且最小损失的恢复(多数闪回功能都能在数据库联机状态下完成)。需要注意的是,闪回技术旨在快速恢复逻辑错误,...
实现该功能的基础为本文要讲述的闪回恢复区( Flash Recovery Area )。闪回恢复区是 Oracle 10g 中的新事物。简单的说,闪回恢复区是一块用以存储恢复相关的文件的存储空间。允许用户集中存储所有恢复相关的文件。
Oracle 9i引入的闪回查询功能显示前一个时间点的查询结果。但是,表本身并没有改变。Oracle 10g增强了闪回功能,可以永久闪回一个表格。现在Oracle 10g第二版通过增加指定的恢复点,使这一过程更加方便。