从Oracle Database 9i开始,通过利用Flashback特征,可以显示过去特定时间点或特定SCN的表数据。
a) 查询时间戳
SELECT systimestamp FROM v$database;
b)当前数据
SELECT * FROM scott.emp where empno = 7788;
c)DML操作
update scott.emp set sal = 2500 where empno = 7788;
d)查询当前数据与指定时间数据
SELECT * FROM scott.emp where empno = 7788;
select sal from scott.emp as of timestamp
to_timestamp('2013-03-20 10:24:00.333 上午', 'YYYY-MM-DD HH:MI:SSXFF AM')
where empno = 7788;
注意,如果使用Flashback查询,那么要求数据库必须采用自动UNDO管理方式。
1. timestampa) 查询时间戳
SELECT systimestamp FROM v$database;
b)当前数据
SELECT * FROM scott.emp where empno = 7788;
c)DML操作
update scott.emp set sal = 2500 where empno = 7788;
d)查询当前数据与指定时间数据
SELECT * FROM scott.emp where empno = 7788;
select sal from scott.emp as of timestamp
to_timestamp('2013-03-20 10:24:00.333 上午', 'YYYY-MM-DD HH:MI:SSXFF AM')
where empno = 7788;
2. SCN
a) 查询SCN
SELECT current_scn FROM v$database;
b)当前数据
SELECT * FROM scott.emp where empno = 7788;
c)DML操作
update scott.emp set sal = 2500 where empno = 7788;
d)查询当前数据与指定时间数据
SELECT * FROM scott.emp where empno = 7788;
select sal from scott.emp as of scn 4980831 where empno = 7788;