flashback version query(待完善）

SQL> create table dep(id number,name varchar2(10));

Table created.

SQL> insert into dep values(1,'dep1');

1 row created.

SQL> commit;

Commit complete.

SQL> select ora_rowscn,id,name from dep;

ORA_ROWSCN???????? ID NAME
---------- ---------- ----------
?? 1445309????????? 1 dep1

SQL> update dep set name='dep11' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select ora_rowscn,name from dep;

ORA_ROWSCN???????? ID NAME
---------- ---------- ----------
?? 1445369????????? 1 dep11

SQL> /

ORA_ROWSCN???????? ID NAME
---------- ---------- ----------
?? 1445369????????? 1 dep11

SQL> update dep set name='dep12' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select ora_rowscn,name from dep;

ORA_ROWSCN???????? ID NAME
---------- ---------- ----------
?? 1445404????????? 1 dep12
col versions_xid format a16 heading 'XID'
col versions_startscn format 99999999 heading 'VSN|start|scn'
col versions_endscn format 99999999 heading 'vsn|end|scn'
col versions_operation format a12 heading 'operation'
select versions_xid,versions_startscn,versions_endscn,
decode(versions_operation,'I','INSERT','U','UPDATE','D','DELETE','ORIGINAL') "OPERATION",
ID,NAME
FROM DEP
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE ID=1;

?????????????????????? VSN?????? vsn
???????????????????? start?????? end
XID??????????????????? scn?????? scn OPERATIO???????? ID NAME
---------------- --------- --------- -------- ---------- ----------
0A000100DF010000?? 1445404?????????? UPDATE??????????? 1 dep12
09000E0020020000?? 1445369?? 1445404 UPDATE??????????? 1 dep11
04002200CC010000?? 1445309?? 1445369 INSERT??????????? 1 dep1

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

通过SCN和STAMPSTAMP查询

SQL> select salary from emp ? 2? versions between ? 3? scn minvalue and maxvalue ? 4? where id=1; ??? SALARY ---------- ?????? 120 ?????? 100 select salary from emp versions between timestamp to_timestamp('2012-04-15 15:30:01','yyyy-mm-dd hh24:mi:ss') and to_timestamp('2012-04-15 17:30:00','yyyy-mm-dd hh24:mi:ss') where id=1;