3.flashback query

(a).flashback query à只能查看一个时间点的

select * from t1 as of timestamp

to_timestamp('2012/06/10 15:18:47','yyyy/mm/dd hh24:mi:ss');

select * from t1 as of timestamp

to_timestamp('2012/06/10 15:18:47','yyyy/mm/dd hh24:mi:ss')

where id not in(select id from t1)

(b).flashback version query à可以查看一个时间段内的

select versions_xid,versions_startscn,versions_endscn,versions_operation,id

,name from t1 versions between scn minvalue and maxvalue;

Ora_rowscn :伪列

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCN ID NAME

---------- ---------- --------------------------------------------------

11477537 1 a

11477537 2 b

11477537 3 dddddd

SQL> update t1 set name='xxxxx' where id=3;

1 row updated.

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCN ID NAME

---------- ---------- --------------------------------------------------

11477537 1 a

11477537 2 b

11477537 3 xxxxx

SQL> commit;

Commit complete.

SQL> select ora_rowscn,id,name from t1;

ORA_ROWSCN ID NAME

---------- ---------- --------------------------------------------------

11477662 1 a

11477662 2 b

11477662 3 xxxxx

从上面例子看出，只有commit之后ora_rowscn才会发生改变。

Ora_rowscn是数据块级别的，一个数据块内所有记录都是一个ora_rowscn,

数据块的任意记录被修改，快内所有记录的ora_rowscn都会改变。

create table t4 (id number(3),name varchar2(10)) rowdependencies;

à确保每行的ora_rowscn都不一样。

T2没有rowdependencies，T4有rowdependencies。

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block,ora_rowscn,id,name

from t2;

FNO BLOCK ORA_ROWSCN ID NAME

---------- ---------- ---------- ---------- ------------------------------

4 9799 11477865 1 a

4 9799 11477865 2 a

4 9799 11477865 3 xxx

SQL> select dbms_rowid.rowid_relative_fno(rowid) fno,dbms_rowid.rowid_block_number(rowid) block,ora_rowscn,id,name

from t4;

FNO BLOCK ORA_ROWSCN ID NAME

---------- ---------- ---------- ---------- ------------------------------

4 9831 11478360 1 a

4 9831 11480351 2 yyyy

4 9831 11478399 3 xxx

alter system dump datafile 4 block 9799; (dump t2)

内容如下：

Start dump data blocks tsn: 4 file#:4 minblk 9799 maxblk 9799

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16787015

BH (0x76fe0778) file#: 4 rdba: 0x01002647 (4/9799) class: 1 ba: 0x76ce6000

set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 450,28

dbwrid: 0 obj: 82452 objn: 82452 tsn: 4 afn: 4 hint: f

hash: [0x76fe0958,0x9094d8b0] lru: [0x76fe0ac0,0x76fe0730]

ckptq: [NULL] fileq: [NULL] objq: [0x76fe0ae8,0x8df6a258]

st: XCURRENT md: NULL tch: 8

flags: block_written_once redo_since_read

LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]

cr pin refcnt: 0 sh pin refcnt: 0

BH (0x76fe08a8) file#: 4 rdba: 0x01002647 (4/9799) class: 1 ba: 0x76ce8000

set: 5 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 450,28

dbwrid: 0 obj: 82452 objn: 82452 tsn: 4 afn: 4 hint: f

hash: [0x78bf5248,0x76fe0828] lru: [0x76fe0d20,0x77bf92a0]

lru-flags: moved_to_tail

ckptq: [NULL] fileq: [NULL] objq: [NULL]

st: CR md: NULL tch: 1

cr: [scn: 0x0.af2363],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.af2363],[sfl: 0x

0],[lc: 0x0.af2358]

flags: redo_since_read

cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 4 rdba: 0x01002647 (4/9799)

scn: 0x0000.00af2369 seq: 0x01 flg: 0x06 tail: 0x23690601

frmt: 0x02 chkval: 0x7ec8 type: 0x06=trans data

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00007FF7F727AA00 to 0x00007FF7F727CA00

7FF7F727AA00 0000A206 01002647 00AF2369 06010000 [....G&..i#......]

7FF7F727AA10 00007EC8 00000001 00014214 00AF2351 [.~.......B..Q#..]

7FF7F727AA20 00000000 00320002 01002640 00140006 [......2.@&......]

7FF7F727AA30 00002301 00C004FC 002807FB 00002003 [.#........(.. ..]

7FF7F727AA40 00AF2369 00000000 00000000 00000000 [i#..............]

7FF7F727AA50 00000000 00000000 000000