How_to_flashback_table_of_gauss100
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
Gaussdb提供了类似Oracle的闪回表功能；可以很好的应对drop table或者truncate table的误操作场景；这个功能非常赞。本质上来讲也是使用了回收站功能。下面进行简单测试：
++清空回收站
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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
|
1 2 3 4 5 6 7 |
SQL> conn roger/Roger007@127.0.0.1:1611 connected. SQL> drop table test; Succeed. |
+++查看回收站内容
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
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 的表能闪回吗 ？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
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之后，被写入数据之后，还能闪回吗？ 下面测试一下。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 |
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了，还能闪回吗？ 我们进一步验证一下呢？
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
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的功能还是非常赞的；唯一不足的是不支持闪回事务查询。
Leave a Reply
You must be logged in to post a comment.