分类： Oracle
2010-04-10 11:32:09
对于flashback query，对表做过DDL操作，包括：drop/modify列, move表, drop分区(如果有的话), truncate table/partition，这些操作会另undo表空间中的撤销数据失效，对于执行过这些操作的表应用flashback
query会触发ORA-01466错误。另外一些表结构修改语句虽然并不会影响到undo表空间中的撤销记录，但有可能因表结构修改导致undo中重做记录无法应用的情况，比如对于增加了约束，而flashback query查询出的undo记录已经不符合新建的约束条件，这个时候直接恢复显然不可能成功，你要么暂时disable约束，要么通过适当逻辑，对要恢复的数据进行处理之后，再执行恢复。
flashback
query分为两种，分别是flashback versions query和flashback transaction query.
flashback
versions query有两种用法：
(1) 查看两个时间点/scn之间表记录的所有版本情况:
select * from table_name versions between timestamp/scn...
如果不知道最早和最晚的时间点或scn,可以用minvalue和maxvalue来代替.
(2) 查看某时间点/scn表中记录情况:
select * from table_name as of timestamp/scn...
flashback
versions query的虚拟列：
versions_startscn、versions_starttime:开始scn或时间点.如果值为NULL表示创建该版本的时间早于指定的scn或时间点。
versions_endscn、versions_endtime:结束scn或时间点.如果值为NULL表示该版本当前还存在或执行的是delete.
versions_xid:事务ID
versions_operation:操作类型.
D=delete、U=update、I=insert
flashback
versions query的限制条件：
不能用于查询外部表、临时表和fixed table.
不能用于视图.
无法跨越表结构修改.
支持IOT表，但IOT表的update将分解成delete和insert.
1、flashback versions query简单
SQL> create table p (id number, name varchar2(10));
Table
created.
SQL>
insert into p values (1,'test1');
1
row created.
SQL>
insert into p values (2,'test2');
1
row created.
SQL>
commit;
Commit
complete.
SQL>
select current_scn from v$database;
CURRENT_SCN
-----------
8127315560
SQL>
insert into p values (3,'test3');
1
row created.
SQL>
update p set name='test20' where id=2;
1
row updated.
SQL>
commit;
Commit
complete.
SQL>
select current_scn from v$database;
CURRENT_SCN
-----------
8127315597
SQL> delete from p where id=3;
1
row deleted.
SQL>
commit;
Commit
complete.
SQL>
select current_scn from v$database;
CURRENT_SCN
-----------
8127315640
SQL>
select * from p;
ID NAME
---------- --------------------
1 test1
2 test20
SQL> select
versions_startscn,versions_endscn,versions_xid,versions_operation,id,name from
p versions between scn 8127315560 and 8127315640;
VERSIONS_STARTSCN
VERSIONS_ENDSCN VERSIONS_XID
VE ID NAME
----------------- --------------- ---------------- -- ----------
--------------------
8127315637
0100280068020100 D
3 test3
8127315594
01001E0069020100 U
2 test20
8127315594
8127315637 01001E0069020100
I 3 test3
1 test1
8127315594
2 test2
flashback
transaction：
flashback transaction查询flashback_transaction_query得到transaction信息.
使用flashback transaction需要select any transaction系统权限.
如果表中有chained rows或是cluster table,则需要enable
supplemental log.
alter database add supplemental log data;
2、flashback transaction测试
SQL>
create table p (id number,sal number);
Table
created.
SQL> insert into p values
(1,100);
1 row created.
SQL>
commit;
Commit
complete.
SQL> select * from
p;
ID
SAL
----------
----------
1
100
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8138851177
SQL> update p set sal=1000 where id=1;
1 row
updated.
SQL> commit;
SQL>
select * from
p;
ID
SAL
---------- ----------
1 1000
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
8138851256
SQL> select
versions_startscn,versions_endscn,versions_xid,versions_operation,id,sal from p
versions between scn 8138851177 and 8138851256;
VERSIONS_STARTSCN
VERSIONS_ENDSCN VERSIONS_XID
VE
ID SAL
----------------- --------------- ---------------- -- ---------- ----------
8138851207
01000E009A020100 U
1 1000
8138851207
1 100
SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
from flashback_transaction_query where xid=hextoraw('&xid');
Enter value for xid: 01000E009A020100
old 1: select
start_scn,commit_scn,logon_user,operation,table_name,undo_sql from
flashback_transaction_query where xid=hextoraw('&xid')
new 1: select
start_scn,commit_scn,logon_user,operation,table_name,undo_sql from
flashback_transaction_query where xid=hextoraw('01000E009A020100')
select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from
flashback_transaction_query where xid=hextoraw('01000E009A020100')
*
ERROR at line 1:
ORA-01031: insufficient privileges
由于没有授权，提示无权限.
SQL>
conn /as sysdba
Connected.
SQL> grant select any transaction to user1;
Grant
succeeded.
SQL>
conn user1/abc
Connected.
SQL>
select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from
flashback_transaction_query where xid=hextoraw('&xid');
Enter value for xid: 01000E009A020100
old 1: select
start_scn,commit_scn,logon_user,operation,table_name,undo_sql from
flashback_transaction_query where xid=hextoraw('&xid')
new 1: select start_scn,commit_scn,logon_user,operation,table_name,undo_sql
from flashback_transaction_query where xid=hextoraw('01000E009A020100')
START_SCN
COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ---------- ---------- ----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
8138851171 8138851207 USER1
UPDATE P
update "USER1"."P" set "SAL" = '100' where ROWID
= 'AAAPn2AAWAAAAAuAAA';
8138851171
8138851207 USER1 BEGIN
SQL>
update "USER1"."P" set "SAL" = '100' where ROWID
= 'AAAPn2AAWAAAAAuAAA';
1
row updated.
SQL>
select * from p;
ID SAL
---------- ----------
1 100
SQL>
commit;
Commit
complete.
使用undo_sql回滚update操作.
