Week 1: Flashback query

最新推荐文章于 2019-08-15 15:21:40 发布

转载最新推荐文章于 2019-08-15 15:21:40 发布 · 79 阅读

本文通过一系列Oracle数据库操作，展示了如何使用闪回查询来查看不同时间点的数据版本，包括创建表、插入数据、更新和删除等操作，并通过具体SQL语句演示了如何获取这些历史版本的信息。

create table rates
(
curr varchar2(4),
rate number(15,10)
)

insert into rates values ('EURO',1.1012);
commit;
update rates set rate = 1.1014;
commit;
update rates set rate = 1.1013;
commit;
delete rates;
commit;
insert into rates values ('EURO',1.1016);
commit;
update rates set rate = 1.1011;
commit;

select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME

select rate, versions_starttime, versions_endtime
from rates versions
between timestamp
to_date('7/26/2010 14:24:20','mm/dd/yyyy hh24:mi:ss')
and to_date('7/26/2010 14:26:55','mm/dd/yyyy hh24:mi:ss')

select versions_starttime, versions_endtime, versions_xid,
versions_operation, rate
from rates versions between timestamp
to_date('7/26/2010 14:24:20', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/

select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '03001500A11A0000';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23895263/viewspace-669477/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23895263/viewspace-669477/