flashback version query(待完善）-优快云博客

本文演示了如何使用SQL在Oracle数据库中创建表、插入数据并进行更新操作。特别地，通过SCN（System Change Number）和时间戳来查询数据的历史版本，包括不同版本的数据状态及其变更记录。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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,id,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,id,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;