达梦数据库学习笔记之 — 闪回查询

达梦数据库也提供了类似Oracle 9i一样的闪回查询功能，不过仅仅是闪回查询（flashback version和flashback transaction），并不支持flashback drop table。这里我们简单测试一下。

首先需要打开flahsback 功能：

SQL>  alter system set 'enable_flashback'=1 both;
DMSQL executed successfully
used time: 6.523(ms). Execute id is 133.
SQL> select PARA_NAME,PARA_VALUE,FILE_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME like '%FLASH%';
 
PARA_NAME        PARA_VALUE FILE_VALUE DESCRIPTION                         
---------------- ---------- ---------- ------------------------------------
ENABLE_FLASHBACK 1          1          Whether to enable flashback function
 
used time: 5.796(ms). Execute id is 134.

跟Oracle Database一样，达梦数据库的闪回也是利用回滚段来进行闪回查询进行构造，因此需要调整undo_rentention参数（参数名称跟oracle也一样）：

SQL> alter system set 'UNDO_RETENTION'=86401;
alter system set 'UNDO_RETENTION'=86401;
[-838]:Invalid parameter value.
used time: 5.055(ms). Execute id is 0.
SQL> alter system set 'UNDO_RETENTION'=86400;
DMSQL executed successfully
used time: 3.263(ms). Execute id is 137.
SQL> select PARA_NAME,PARA_VALUE,FILE_VALUE,DESCRIPTION from v$dm_ini where PARA_NAME like '%UNDO%';
 
PARA_NAME       PARA_VALUE   FILE_VALUE DESCRIPTION                                                                             
--------------- ------------ ---------- ----------------------------------------------------------------------------------------
UNDO_EXTENT_NUM 4            4          Number of initial undo extents for each worker thread
UNDO_RETENTION  86400.000000 90.000000  Maximum retention time in seconds for undo pages since relative transaction is committed
 
used time: 5.531(ms). Execute id is 138.

不过在达梦数据库中，对该参数进行了控制，最大值是86400，即1天。

接下来我们创建一个测试表试试闪回查询功能：

SQL> create table test0826 as select * from dba_objects;
executed successfully
used time: 60.683(ms). Execute id is 146.
SQL> select sysdate from dual;
 
SYSDATE                                                                                             
----------------------------------------------------------------------------------------------------
2021-08-26 16:31:32
 
used time: 1.245(ms). Execute id is 148.
SQL> select owner,object_name,object_id from dba_objects where rownum < 3;
 
OWNER        OBJECT_NAME  OBJECT_ID
------------ ------------ ---------
BENCHMARKSQL BENCHMARKSQL 150995944
CTISYS       CTISYS       150994948
 
used time: 9.601(ms). Execute id is 149.
SQL> update test0826 set OBJECT_NAME='www.enmotech.com' where object_id=150995944;
affect rows 1
 
used time: 1.653(ms). Execute id is 150.
SQL> commit;
executed successfully
used time: 4.446(ms). Execute id is 151.
SQL> select sysdate from dual;
 
SYSDATE                                                                                             
----------------------------------------------------------------------------------------------------
2021-08-26 16:32:45
 
used time: 0.618(ms). Execute id is 152.
 
SQL> select COMMIT_TRXID,COMMIT_TIMESTAMP,OPERATION,TABLE_NAME,UNDO_SQL from V$FLASHBACK_TRX_INFO where OPERATION='U';
 
COMMIT_TRXID         COMMIT_TIMESTAMP                                                                                     OPERATION TABLE_NAME
-------------------- ---------------------------------------------------------------------------------------------------- --------- ----------
UNDO_SQL                                                                 
-------------------------------------------------------------------------
841038               2021-08-26 01:32:40.494000                                                                           U         TEST0826
UPDATE SYSDBA.TEST0826 SET OBJECT_NAME='BENCHMARKSQL' WHERE ROWID=9947649
 
 
used time: 2.894(ms). Execute id is 160.
 
SQL> select owner,object_name,object_id
2   from test0826 when
3    TIMESTAMP  '2021-08-26 01:32:30' 
4     WHERE owner='BENCHMARKSQL' and object_id like '15099%'
5   /
 
OWNER        OBJECT_NAME  OBJECT_ID
------------ ------------ ---------
BENCHMARKSQL BENCHMARKSQL 150995944
 
used time: 2.813(ms). Execute id is 164.

可以看到功能方面跟Oracle类似。可以用来应对一些误操作，比如dml的恢复。

总的来看，达梦数据库的闪回技术还是比较原始，停留在Oracle 9i这个水平。不过有总比没有好，实际上很多国产数据库连闪回功能都还不具备。

希望国产数据库越来越强。