- 配置文件添加参数
db_recyclebin=true
enable_flashback_query=on
track_commit_timestamp=on
2.创建测试数据
create table t16(id int,name char);
insert into t16 values(1,‘a’);
select sys_sleep(1);
insert into t16 values(2,‘b’);
select sys_sleep(1);
3.设置闪回时间点
闪回数据
1
flashback table t16 to timestamp(systimestamp - interval ‘2’ second);
select * from t16;
TEST=# select * from t16;
ID | NAME
----±-----
1 | a
(1 row)
---------------时间interval 需要
flashback table t16 to timestamp(systimestamp - interval ‘2’ second);
select * from t16;
2 闪回表
drop table t16;
select * from t16;
flashback table t16 to before drop;
select * from t16;
create table ttt(c int not null) ;
drop table ttt;
flashback table ttt to before drop;
create table ttt2(c int);
create index idx1 on ttt2©;
drop table ttt2;
flashback table ttt2 to before drop;
select relfilnode from sys_class where relfilnode = 16393; (表,约束,索引)
3闪回查询(两种方式)
create table t29(id int);
CREATE TABLE
TEST=> insert into t29 values(1);
INSERT 0 1
TEST=> select sys_sleep(1);
SYS_SLEEP
(1 row)
TEST=> select * from t29 versions between timestamp now() - interval ‘2 second’ and timestamp now();
ID
1
(1 row)
TEST=> delete from t29;
DELETE 1
TEST=> select * from t29 versions as of timestamp now() - interval ‘8 second’;
ID
1
(1 row)