問題描述

誤操作后，閃回查詢到某一時間點提示:“[-9801]:flashback version has been out of date.”

SQL> SELECT * FROM PERSON_TYPE WHEN TIMESTAMP '2023-11-23 18:51:41';
SELECT * FROM PERSON_TYPE WHEN TIMESTAMP '2023-11-23 18:51:41';
[-9801]:flashback version has been out of date.
used time: 0.548(ms). Execute id is 0.

問題排查

過了UNDO_RETENTION時間

--查詢UNDO_RETENTION參數
select name,type,value from v$parameter where name = 'UNDO_RETENTION';LINEID     name           TYPE VALUE    
---------- -------------- ---- ---------
1          UNDO_RETENTION SYS  90.000000

有博主寫的默認900秒，15分鐘，但是官方文檔寫的是90秒。如果顯示的是90說明沒有更改參數過了UNDO_RETENTION則無法進行閃回查詢。不重啟也驗證了可以閃回查詢。

解決辦法

--更改UNDO_RETENTION參數
alter system set 'UNDO_RETENTION'=3600 both;
select name,type,value from v$parameter where name = 'UNDO_RETENTION';--重啟生效(可選)
[dmdba@localhost ~]$ /dmdb8/dmdbms/bin/DmServicefuwa stop
Stopping DmServicefuwa:                                    [ OK ]
[dmdba@localhost ~]$ /dmdb8/dmdbms/bin/DmServicefuwa start
Starting DmServicefuwa: 

附：開啟閃回功能

--開啟閃回功能
select name,type,value from v$parameter where name='ENABLE_FLASHBACK';
或
select para_value from v$dm_ini where para_name='ENABLE_FLASHBACK';  # 0未開啟 1開啟alter system set 'ENABLE_FLASHBACK'=1 both; 
或
sf_set_system_para_value('ENABLE_FLASHBACK',1,0,1);
或
或者修改參數文件dm.ini中ENABLE_FLASHBACK值為1--重啟生效
[dmdba@localhost ~]$ /dmdb8/dmdbms/bin/DmServicefuwa stop
Stopping DmServicefuwa:                                    [ OK ]
[dmdba@localhost ~]$ /dmdb8/dmdbms/bin/DmServicefuwa start
Starting DmServicefuwa: [ OK ]--修改UNDO_RETENTION
SQL> select name,type,value from v$parameter where name = 'UNDO_RETENTION';LINEID     NAME           TYPE VALUE    
---------- -------------- ---- ---------
1          UNDO_RETENTION SYS  90.000000  
SQL> alter system set 'UNDO_RETENTION'=3600 both;SQL> select name,type,value from v$parameter where name = 'UNDO_RETENTION';LINEID     NAME           TYPE VALUE      
---------- -------------- ---- -----------
1          UNDO_RETENTION SYS  3600.000000

注意

更改ENABLE_FLASHBACK和ENABLE_FLASHBACK參數后重啟與否不影響閃回查詢。

--更改參數后未重啟查看參數文件dm.ini
發現ENABLE_FLASHBACK=0UNDO_RETENTION=90--重啟查看參數文件dm.ini
發現ENABLE_FLASHBACK=1UNDO_RETENTION=3600