GaussDB T 的 Flashback Table 功能非常强劲可以闪回TRUNCATE

原创李真旭 2020-02-16

1316

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> 
复制