玉面飞龙的BLOG

10g的新功能。如果错误的修改了表的数据，且之前没有对受影响的数据进行备份。可以选择flashback table作为表不完全恢复的方法。

1)首先,flashback table将基于flashback SCN 或者 time stamp 从 undo tablespace 中读取指定恢复时间戳的原始映像的ROWID和是否改动的情况，并将信息存储在一个临时表 SYS_TEMP_FBT.

2)然后,在当前表中删除应该要删除的纪录。这些被删除的纪录是指定恢复时间戳之后update和insert过的数据.

3)最后,通过flashback query和临时表SYS_TEMP_FBT,得到指定恢复时间戳那时存在的且在步骤2中被删除的纪录。

描述的点复杂。

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BINZHANG TABLE

SQL> !date
Tue Oct 21 22:53:57 GMT 2008

SQL> begin
2 for i in 1..1000 loop
3 delete from binzhang where id=i;
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> select count(*) from binzhang;
COUNT(*)
———-
10727

SQL> alter table binzhang ENABLE ROW MOVEMENT;
Table altered.

SQL> create table a as select * from v$Mystat;
Table created.

SQL> flashback table binzhang TO TIMESTAMP TO_TIMESTAMP(’2008-10-21 22:53:00′,’yyyy-mm-dd hh24:mi:ss’);
Flashback complete.

SQL> create table b as select * from v$Mystat;
Table created.

SQL> select count(*) from binzhang;
COUNT(*)
———-
11727

具体的信息要从trace中来观察。

flashback操作完成后,可以发现schema多了一个临时表。

SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
BINZHANG TABLE
SYS_TEMP_FBT TABLE

SQL> select count(*) from SYS_TEMP_FBT;
COUNT(*)
———-
0

SQL> desc SYS_TEMP_FBT
Name Null? Type
—————————————– ——– —————————-
SCHEMA VARCHAR2(32)
OBJECT_NAME VARCHAR2(32)
OBJECT# NUMBER
RID ROWID
ACTION CHAR(1)

SQL> drop table SYS_TEMP_FBT;
Table dropped.

以下是从trace中看到的

truncate table SYS_TEMP_FBT

INSERT /*+ APPEND */ into SYS_TEMP_FBT SELECT /*+ FBTSCAN FULL(S) PARALLEL(S,DEFAULT) */ :1, :2, :3, rowid, SYS_FBT_INSDEL FROM “XFAN”.”BINZHANG” as of SCN :4 S

Rows Row Source Operation
——- —————————————————
1 LOAD AS SELECT (cr=3 pr=0 pw=0 time=0 us)
2198 PX COORDINATOR (cr=3 pr=0 pw=0 time=24 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)
0 TABLE ACCESS FULL BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=3 size=20592 card=1716)

DELETE /*+ BYPASS_UJVC */ FROM (SELECT /*+ ORDERED USE_NL(S) PARALLEL(S,DEFAULT) PARALLEL(T,DEFAULT) */ S.rowid FROM SYS_TEMP_FBT T,”XFAN”.”BINZHANG” S WHERE T.rid = S.rowid and T.action = ‘D’ and T.object# = : 1) V

Rows Row Source Operation
——- —————————————————
0 DELETE BINZHANG (cr=2 pr=4 pw=4 time=0 us)
599 PX COORDINATOR (cr=2 pr=0 pw=0 time=23 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=3 size=2179177 card=539)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=24108 card=6)
0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=2250 card=90)

INSERT /*+ PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ INTO “XFAN”.”BINZHANG” SELECT /*+ USE_NL(S) ORDERED PARALLEL(S, DEFAULT) PARALLEL(T, DEFAULT) */ S.* FROM SYS_TEMP_FBT T , “XFAN”.”BINZHANG” as of SCN :1 S WHERE T.rid = S.rowid and T.action = ‘I’ and T.object# = :2

Rows Row Source Operation
——- —————————————————
0 LOAD TABLE CONVENTIONAL (cr=7 pr=0 pw=0 time=0 us)
1599 PX COORDINATOR (cr=2 pr=0 pw=0 time=31 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=4 size=1107782 card=274)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL SYS_TEMP_FBT (cr=0 pr=0 pw=0 time=0 us cost=2 size=64288 card=16)
0 TABLE ACCESS BY USER ROWID BINZHANG (cr=0 pr=0 pw=0 time=0 us cost=1 size=425 card=17)

Oracle通过在flashback query接口上封装了一下，实现了flashback table的功能。

从SQL PLAN中也可以看到，flashback至少需要在表上作一次full table scan,如果表很大而且修改频繁，其IO成本可想而知。

还可以研究一下SYS_FBT_INSDEL function and FBTSCAN hint