验证GaussDB T 闪回事务查询功能；闪回表功能强劲闪回TRUNCATE
验证GaussDB T 闪回事务查询功能；闪回表功能强劲闪回TRUNCATE
数据和云
发布于 2020-02-24 17:55:27
发布于 2020-02-24 17:55:27
一、验证 GaussDB T 支持闪回事务查询功能
环境：redhat7.5+gaussdb100
1. 创建表：
SQL> conn wsx/password@127.0.0.1:
connected.
SQL> create table wsx.t1 as select * from dual;
Succeed.
2. 模拟delete操作：
SQL> delete from wsx.t1;
1 rows affected.
SQL>
SQL>
SQL> commit;
Succeed.
SQL> select * from t1;
DUMMY
-----
0 rows fetched.
SQL> select sysdate from dual;
SYSDATE
----------------------
2020-02-18 17:05:56
1 rows fetched.
3. 查看回收站是否有记录。
SQL> col org_name format a10;
SQL>
SQL> col partition_name format a10;
SQL>
SQL>
SQL> conn / as sysdba
connected.
SQL>
SQL> select name,user#,org_name,partition_name,operation#,flags from sys_recyclebin;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ ---------- ---------- ------------ ------------
0 rows fetched.
4.闪回查询SQL> SELECT * FROM wsx.t1
AS OF TIMESTAMP TO_DATE ('2020/02/18 17:04:00', 'yyyy/mm/dd hh24:mi:ss');
DUMMY
-----
X
rows fetched.
5.恢复表wsx.t1在2020.02.18 17:04:00误删除的记录：
SQL> conn wsx/password@127.0.0.1:1888
connected.
SQL>
SQL>
SQL> insert into wsx.t1
(SELECT * FROM wsx.t1
AS OF TIMESTAMP TO_DATE ('2020/02/18 17:04:00', 'yyyy/mm/dd hh24:mi:ss'));
1 rows affected.
SQL>
SQL> commit;
Succeed.
SQL>
SQL>
SQL> select * from wsx.t1;
DUMMY
-----
X
1 rows fetched.
----可以看到，表wsx.t1已经有记录了
总的来说，gaussdb100 T 是可以支持闪回事务查询。 二、GaussDB T 的 Flashback Table 功能非常强劲可以闪回TRUNCATE Gaussdb提供了类似Oracle的闪回表功能；可以很好的应对drop table或者truncate table的误操作场景；这个功能非常赞。本质上来讲也是使用了回收站功能。下面进行简单测试： 清空回收站
SQL> select * from v$version;
VERSION
----------------------------------------------------------------
GaussDB_100_1.0.1.SPC2.B003 Release ae9d6c
ZENGINE
ae9d6c
rows fetched.
SQL> purge recyclebin;
Succeed.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
rows fetched.
drop table
SQL> conn roger/Roger007@127.0.0.1:1611
connected.
SQL> drop table test;
Succeed.
查看回收站内容
SQL> conn / as sysdba
connected.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
BIN$0$88F6E==$0 IDX_TEST_ID
BIN$0$88F6D6==$0 TEST
rows fetched.
闪回被drop table
SQL> flashback table roger.test to before drop;
Succeed.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
0 rows fetched.
SQL> select * from roger.test;
A B
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 rows fetched.
那么truncate 的表能闪回吗 ？
SQL> create table roger.test_copy as select * from roger.test ;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
www.enmotech.com
www.killdb.com
www.modb.pro
rows fetched.
SQL> truncate table roger.test_copy;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
rows fetched.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
BIN$$FFA4==$ TEST_COPY
rows fetched.
SQL> flashback table roger.test_copy to before truncate force;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
www.enmotech.com
www.killdb.com
www.modb.pro
rows fetched.
SQL>
可以看到成功闪回了被truncate table。
那么如果表被truncate之后，被写入数据之后，还能闪回吗？ 下面测试一下。
SQL> create table roger.test_copy2 as select * from roger.test ;
Succeed.
SQL> select * from roger.test_copy2;
A B
---------------------------------------- --------------------
www.enmotech.com
www.killdb.com
www.modb.pro
rows fetched.
SQL> truncate table roger.test_copy2;
Succeed.
SQL> insert into roger.test_copy2 values(,'www.baidu.com');
rows affected.
SQL> commit;
Succeed.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
BIN$$==$ TEST_COPY2
rows fetched.
SQL> flashback table roger.test_copy2 to before truncate force;
Succeed.
SQL> select * from roger.test_copy2;
A B
---------------------------------------- --------------------
www.enmotech.com
www.killdb.com
www.modb.pro
rows fetched.
可以看到非常强大；仍然可以进行闪回。。。。 这样妈妈再也不用担心数据被truncate了。。。。
那么如果表被ddl change了，还能闪回吗？ 我们进一步验证一下呢？
SQL> create table roger.test_copy3 as select * from roger.test;
Succeed.
SQL> select * from roger.test_copy3;
A B
---------------------------------------- --------------------
www.enmotech.com
www.killdb.com
www.modb.pro
rows fetched.
SQL> desc roger.test_copy3
Name Null? Type
----------------------------------- -------- ------------------------------------
A NUMBER
B VARCHAR( BYTE)
SQL> truncate table roger.test_copy3;
Succeed.
SQL> alter table roger.test_copy3 modify (b VARCHAR());
Succeed.
SQL> insert into roger.test_copy3 values(,'support.enmotech.com');
rows affected.
SQL> commit;
Succeed.
SQL> select * from roger.test_copy3;
A B
---------------------------------------- ------------------------------
support.enmotech.com
rows fetched.
SQL> flashback table roger.test_copy3 to before truncate force;
GS-00732, The table definition of ROGER.TEST_COPY3 has been changed.
SQL>
SQL> alter table roger.test_copy3 modify (b VARCHAR());
GS-00805, Column B is not empty in table TEST_COPY3
SQL>
SQL> delete from roger.test_copy3;
rows affected.
SQL> commit;
Succeed.
SQL> alter table roger.test_copy3 modify (b VARCHAR());
Succeed.
SQL> flashback table roger.test_copy3 to before truncate force;
GS-00732, The table definition of ROGER.TEST_COPY3 has been changed.
SQL>
可以看到，如果表进行了ddl 变更，即表定义发生了改变，就不再能进行flashback了。
总的来说，gaussdb这个flashback table的功能还是非常赞的。
本文根据墨天轮（www.modb.pro）两篇文章整理而得
原文参考：
- https://www.modb.pro/db/21656
- https://www.modb.pro/db/21559
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-02-19，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读