达梦8闪回查询表

开启闪回及修改UNDO_RETENTION，UNDO_RETENTION默认值90秒

select para_name,para_value from v$dm_ini where para_name='ENABLE_FLASHBACK';
LINEID     PARA_NAME        PARA_VALUE
---------- ---------------- ----------
1          ENABLE_FLASHBACK 0


alter system set 'ENABLE_FLASHBACK'=1 both;
select * from v$parameter where name like '%UNDO%';
select para_name,para_value from v$dm_ini where para_name like '%UNDO%';
alter system set 'UNDO_RETENTION'=900 both;

　　

插入数据及删除数据

insert into t1 select id,name from tt limit 10;
SQL> select * from t1;

LINEID     ID          NAME                       
---------- ----------- ---------------------------
1          268436667   ##TMP_TBL_FOR_DBMS_LOB_BLOB
2          268436666   ##TMP_TBL_FOR_DBMS_LOB_CLOB
3          268436636   <ADT_1>
4          268436508   ALL_ALL_TABLES
5          268436601   ALL_ARGUMENTS
6          268436538   ALL_COL_COMMENTS
7          268436517   ALL_COL_PRIVS
8          268436525   ALL_CONSTRAINTS
9          268436565   ALL_CONS_COLUMNS
10         268436497   ALL_DB_LINKS

SQL>  select sysdate from dual;

LINEID     SYSDATE            
---------- -------------------
1          2022-08-18 17:30:02

used time: 0.450(ms). Execute id is 1708.
SQL>          
SQL> delete from t1 where name like '%ALL%';
affect rows 7

used time: 0.301(ms). Execute id is 1709.
SQL> commit;
executed successfully
used time: 8.504(ms). Execute id is 1710.
SQL>  select sysdate from dual;

LINEID     SYSDATE            
---------- -------------------
1          2022-08-18 17:31:19

　　查询数据

SQL> select * from t1;

LINEID     ID          NAME                       
---------- ----------- ---------------------------
1          268436667   ##TMP_TBL_FOR_DBMS_LOB_BLOB
2          268436666   ##TMP_TBL_FOR_DBMS_LOB_CLOB
3          268436636   <ADT_1>

　　闪回查询

SQL> SELECT * FROM t1 WHEN TIMESTAMP '2022-08-18 17:30:02';

LINEID     ID          NAME                       
---------- ----------- ---------------------------
1          268436667   ##TMP_TBL_FOR_DBMS_LOB_BLOB
2          268436666   ##TMP_TBL_FOR_DBMS_LOB_CLOB
3          268436636   <ADT_1>
4          268436508   ALL_ALL_TABLES
5          268436601   ALL_ARGUMENTS
6          268436538   ALL_COL_COMMENTS
7          268436517   ALL_COL_PRIVS
8          268436525   ALL_CONSTRAINTS
9          268436565   ALL_CONS_COLUMNS
10         268436497   ALL_DB_LINKS

　　闪回版本查询

SQL> SELECT VERSIONS_ENDTRXID,name FROM t1 versions between TIMESTAMP '2022-08-18 17:30:02' and sysdate;

LINEID     VERSIONS_ENDTRXID    NAME                       
---------- -------------------- ---------------------------
1          NULL                 ##TMP_TBL_FOR_DBMS_LOB_BLOB
2          NULL                 ##TMP_TBL_FOR_DBMS_LOB_CLOB
3          NULL                 <ADT_1>
4          NULL                 ALL_ALL_TABLES
5          NULL                 ALL_ARGUMENTS
6          NULL                 ALL_COL_COMMENTS
7          NULL                 ALL_COL_PRIVS
8          NULL                 ALL_CONSTRAINTS
9          NULL                 ALL_CONS_COLUMNS
10         NULL                 ALL_DB_LINKS
11         36136                ALL_ALL_TABLES

LINEID     VERSIONS_ENDTRXID    NAME            
---------- -------------------- ----------------
12         36136                ALL_ARGUMENTS
13         36136                ALL_COL_COMMENTS
14         36136                ALL_COL_PRIVS
15         36136                ALL_CONSTRAINTS
16         36136                ALL_CONS_COLUMNS
17         36136                ALL_DB_LINKS

　　闪回事务查询

SQL>  SELECT * FROM V$FLASHBACK_TRX_INFO WHERE COMMIT_TIMESTAMP > '2022-08-18 17:30:02';
no rows

used time: 0.888(ms). Execute id is 2402.