oracle是怎样flashback table的?

在flashback table以后，会发现用户下有一个名为sys_temp_fbt的glocal temporary table.该表记录了如何flashback table.
sys_temp_fbt包含以下五列：
schema :　用户名/模式名
object_name : 表名
object# : object id
rid : rowid. 当action为D时，rowid表示当前表的rowid;当action为I时，rowid表示过去时刻的rowid
action : 操作. D为delete　I为insert
insert语句rollback是delete;delete语句rollback是insert;update语句的rollback是delete新值再insert旧值.
因此sys_temp_fbt表中的action字段只在delete和insert.

flashback table的过程：
1、创建sys_temp_fbt临时表,将rowid和恢复操作记录在表中.
2、将sys_temp_ftb表与当前表关联(根据rowid),删除当前表中多余数据.
3、利用flashback query功能,将sys_temp_ftb表与过去时刻的表关联(根据rowid),插入缺少的数据，则表恢复到过去时刻.

测试过程：

SQL> create table p (id number);

Table created.

SQL> insert into p values (1);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> insert into p values (2);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,p.* from p;

ROWID                      ID
------------------ ----------
AAAPjWAAWAAAABAAAA          1
AAAPjWAAWAAAABAAAB          2

过去时刻的rowid.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
 8082611545

SQL> insert into p values (3);

1 row created.

SQL> delete from p where id=2;

1 row deleted.

SQL> update p set id=10 where id=1;

1 row updated.

SQL> commit;

Commit complete.
分别insert、delete、update一条记录

SQL> select rowid,p.* from p;

ROWID                      ID
------------------ ----------
AAAPjWAAWAAAAAdAAA          3
AAAPjWAAWAAAABAAAA         10
当前rowid

SQL> alter table p enable row movement;

Table altered.

SQL> alter session set events='10046 trace name context forever,level 12';

Session altered.

SQL> flashback table p to scn 8082611545;

Flashback complete.

SQL> select rowid,p.* from p;

ROWID                      ID
------------------ ----------
AAAPjWAAWAAAAAdAAB          1
AAAPjWAAWAAAAAdAAC          2
表已经恢复,但rowid与原来的rowid并不相同.

SQL> alter session set events='10046 trace name context off';

Session altered.

SQL> select * from sys_temp_fbt;

SCHEMA     OBJECT_NAM    OBJECT# RID                        AC
---------- ---------- ---------- ------------------------ 　--
USER1      P               63702 AAAPjWAAWAAAABAAAA(当前)   D
USER1      P               63702 AAAPjWAAWAAAABAAAA(过去)   I
USER1      P               63702 AAAPjWAAWAAAABAAAB(过去)   I
USER1      P               63702 AAAPjWAAWAAAAAdAAA(当前)   D
表中记录的含义：
记录1：AAAPjWAAWAAAABAAAA表示当前表中的记录10,它是update的新值，rollback时要删除该值.
记录2: AAAPjWAAWAAAABAAAA表示过去表中的记录1,它是update的旧值，rollback时要插入该值.插入后rowid发生了变化,变为AAAPjWAAWAAAAAdAAB.
记录3：AAAPjWAAWAAAABAAAB表示过去表中的记录2,它是delete的记录，rollback时要重新插入该记录,rowid变为AAAPjWAAWAAAAAdAAC
记录4：AAAPjWAAWAAAAAdAAA表示当前表中的记录3,它是insert的记录，rollback时删除即可.
实际执行时并不是按照1、2、3、4的顺序执行的，而是先delete再insert.
下面看一下trace文件：
...
创建sys_temp_fbt表
PARSING IN CURSOR #1 len=156 dep=1 uid=71 ct=1 lid=71 tim=1170564360625662 hv=2879521877 ad='5bc156b0'
create global temporary table SYS_TEMP_FBT(schema varchar(32), object_name varchar(32), object# number, rid urowid,  action char(1)) on commit preserve rows
END OF STMT
PARSE #1:c=3000,e=2188,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1170564360625658
BINDS #1:
...
插入数据
PARSING IN CURSOR #3 len=155 dep=1 uid=71 ct=2 lid=71 tim=1170564360648887 hv=1378645651 ad='5e625b2c'
INSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S, DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM "USER1"."P" as of SCN :4 S
END OF STMT
PARSE #3:c=3000,e=2030,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1170564360648882
...
先做delete
PARSING IN CURSOR #5 len=235 dep=1 uid=71 ct=7 lid=71 tim=1170564361134594 hv=2473863531 ad='5b50b268'
/* Flashback Table */ DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T, "USER1"."P" S WHERE T.rid = S.rowid and T.action = 'D' and T.object#  = : 1) V
END OF STMT
PARSE #5:c=2000,e=2114,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1170564361134590
...
再做insert
PARSING IN CURSOR #4 len=282 dep=1 uid=71 ct=2 lid=71 tim=1170564361190755 hv=3573411949 ad='4bd84398'
/* Flashback Table */ INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO "USER1"."P" SELECT /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.* FROM SYS_TEMP_FBT T , "USER1"."P" as of SCN :1 S WHERE T.rid = S.rowid and T.action = 'I' and T.object# = :2
END OF STMT
PARSE #4:c=3000,e=2159,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=1170564361190751

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16566331/viewspace-686887/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16566331/viewspace-686887/