How_to_flashback_table_of_gauss100

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.

+++闪回被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

---------------------------------------- --------------------

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>

可以看到成功闪回了被truncate table。

那么如果表被truncate之后，被写入数据之后，还能闪回吗？ 下面测试一下。

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的功能还是非常赞的；唯一不足的是不支持闪回事务查询。