达梦8闪回查询表

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

?

1 2 3 4 5 6 7 8 9

select para_name,para_value from v$dm_ini where para_name='ENABLE_FLASHBACK'; LINEID     PARA_NAME        PARA_VALUE ---------- ---------------- ---------- 1          ENABLE_FLASHBACK 0 <br> 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;

　　

插入数据及删除数据

?

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

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

　　查询数据

?

1 2 3 4 5 6 7

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>

　　闪回查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

　　闪回版本查询

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

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

　　闪回事务查询

?

1 2 3 4

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.