验证GaussDB T 闪回事务查询功能；闪回表功能强劲闪回TRUNCATE
【摘要】 一、验证 GaussDB T 支持闪回事务查询功能环境：redhat7.5+gaussdb1001. 创建表：SQL> conn wsx/password@127.0.0.1:1888connected.SQL> create table wsx.t1 as select * from dual;Succeed.2. 模拟delete操作：SQL> delete from wsx.t1;1 ...
SQL> conn wsx/password@127.0.0.1:1888
connected.
SQL> create table wsx.t1 as select * from dual;
Succeed.
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.
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
1 rows fetched.
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已经有记录了
二、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 3ae9d6c
ZENGINE
3ae9d6c
3 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
------------------------------ ------------ -------------------- ---------- ------------ ------------
0 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$88F6E0==$0 2 IDX_TEST_ID 1 2
BIN$0$88F6D6==$0 2 TEST 1 3
2 rows fetched.
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.SQL> create table roger.test_copy as select * from roger.test ;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 rows fetched.
SQL> truncate table roger.test_copy;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
0 rows fetched.
SQL> select name,USER#,ORG_NAME,PARTITION_NAME,OPERATION#,FLAGS from SYS_RECYCLEBIN;
NAME USER# ORG_NAME PARTITION_ OPERATION# FLAGS
------------------------------ ------------ -------------------- ---------- ------------ ------------
BIN$1$88FFA4==$0 2 TEST_COPY 0 3
1 rows fetched.
SQL> flashback table roger.test_copy to before truncate force;
Succeed.
SQL> select * from roger.test_copy;
A B
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 rows fetched.
SQL>
SQL> create table roger.test_copy2 as select * from roger.test ;
Succeed.
SQL> select * from roger.test_copy2;
A B
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 rows fetched.
SQL> truncate table roger.test_copy2;
Succeed.
SQL> insert into roger.test_copy2 values(999,'www.baidu.com');
1 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$2$890165==$0 2 TEST_COPY2 0 3
1 rows fetched.
SQL> flashback table roger.test_copy2 to before truncate force;
Succeed.
SQL> select * from roger.test_copy2;
A B
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 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
---------------------------------------- --------------------
1 www.enmotech.com
1 www.killdb.com
666 www.modb.pro
3 rows fetched.
SQL> desc roger.test_copy3
Name Null? Type
----------------------------------- -------- ------------------------------------
A NUMBER
B VARCHAR(20 BYTE)
SQL> truncate table roger.test_copy3;
Succeed.
SQL> alter table roger.test_copy3 modify (b VARCHAR(30));
Succeed.
SQL> insert into roger.test_copy3 values(55,'support.enmotech.com');
1 rows affected.
SQL> commit;
Succeed.
SQL> select * from roger.test_copy3;
A B
---------------------------------------- ------------------------------
55 support.enmotech.com
1 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(20));
GS-00805, Column B is not empty in table TEST_COPY3
SQL>
SQL> delete from roger.test_copy3;
1 rows affected.
SQL> commit;
Succeed.
SQL> alter table roger.test_copy3 modify (b VARCHAR(20));
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的功能还是非常赞的。
转自“墨天轮”
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）