8a 数据闪回

/*
TC-SEC-40001：闪回功能
*/

-- 找回被truncate数据
gccli -ugbasedba -pgbasedbapwd
drop database if exists testflash;
create database testflash;
use testflash;
drop table if exists test_f1;
create table test_f1(i int,j int,v varchar(10),d timestamp) with flashback;
insert into test_f1 values(12,34,'abc',now()),(90,22,'mnb12',now());
insert into test_f1 values(45,134,'dfr12',now());
insert into test_f1 values(125,9,'sdd11',now());
select * from test_f1;
truncate table test_f1;
select * from test_f1;
timetravle table test_f1 to before truncate;
select * from test_f1;

-- 找回被drop数据
drop table if exists test_f2;
create table test_f2(i int,j int,v varchar(10),d timestamp) with flashback;
insert into test_f2 values(12,34,'abc',now()),(90,22,'mnb12',now());
insert into test_f2 values(45,134,'dfr12',now());
insert into test_f2 values(125,9,'sdd11',now());
select * from test_f2;
drop table test_f2;
timetravle table test_f2 to before drop;
select * from test_f2;

-- 找回某个时间点之前的数据
create table test_f3(a int,btime timestamp) with flashback;
insert into test_f3 values(1,now());
select * from test_f3;
insert into test_f3 values(2,now());
select * from test_f3;
delete from test_f3;
select * from test_f3;
select * from gclusterdb.flash_tables where db_name='testflash' and tb_name='test_f3' order by dt;
select * from test_f3 timetravle timestamp to date_format('2024-06-06 15:53:46.190107','%Y-%m-%d %H:%i:%s.%f');
select * from test_f3 timetravle timestamp to date_format('2024-06-06 15:54:48.174396','%Y-%m-%d %H:%i:%s.%f');

-- 找回某个scn号之前的数据
select * from gclusterdb.flash_tables where db_name='testflash' and tb_name='test_f3' order by dt;
select * from test_f3 timetravle scn 3107;
select * from test_f3 timetravle scn 3109;
insert into test_f3 values(3,now());
select * from test_f3 timetravle scn 3111;

-- 清理环境
gccli -ugbasedba -pgbasedbapwd
use testflash;
drop table test_f1 force;
drop table test_f2 force;
drop table test_f3 force;
drop database if exists testflash;