达梦8闪回查询表

达梦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.