ORA-01555 caused by SQL statement below

Alert.log今天产生大量的

ORA-01555 caused by SQL statement below (SQL ID: atfsm4urynvf2, Query Duration=0 sec, SCN: 0x0087.1d1eafdc):
select * from askey_id_mapping as of timestamp sysdate - 2/24 where ID_VALUE = ‘JCYVD30001655CN’
Sat Dec 08 10:43:43 2018
ORA-01555 caused by SQL statement below (SQL ID: cx70v53dbq51x, Query Duration=0 sec, SCN: 0x0087.1c7e4165):
select * from askey_id_mapping as of timestamp sysdate - 4/24 where ID_VALUE = ‘JCYVD30001655CN’
Sat Dec 08 10:44:14 2018
Thread 1 advanced to log sequence 377657 (LGWR switch)
Current log# 1 seq# 377657 mem# 0: +NSFC3DB_ARCH/nsfc3db/onlinelog/group_1.267.860580217
Sat Dec 08 10:44:15 2018

就是一个查询要访问某个数据块，而这个数据块在这个查询执行过程中修改过，那么该查询需要查询undo中数据块，而undo中该数据块已经不存在，从而出现ORA-1555。

解决办法：
Case 1 – Rollback Overwritten
1.缩短sql运行时间
2.增加undo_retention，这个同时需要考虑undo空间大小
3.减少commit(rollback)次数
4.在一条sql中尽量使数据块访问一次
4.1)Using a full table scan rather than an index lookup
4.2)Introducing a dummy sort so that we retrieve all the data, sort it and then sequentially visit these data blocks.

Case 2 – Rollback Transaction Slot Overwritten
这种问题，主要是延迟块清理导致，一般建议在进行大批量的dml操作后，使用全表(全index)扫描执行一遍，或者收集全部统计信息

对于误删恢复数据：
select * from askey_id_mapping as of timestamp to_timestamp(‘2018-12-08 10:30:30’,‘yyyy-mm-dd hh24:mi:ss’)

查询长事物：
select * from gv$transaction —check transaction number

-----------check transaction running time
set linesize 200
set pagesize 5000
col transaction_duration format a45

with transaction_details as
( select inst_id
, ses_addr
, sysdate - start_date as diff
from gvKaTeX parse error: Expected 'EOF', got '#' at position 416: ….sid , s.serial#̲ from gvsession s
, transaction_details t
where s.inst_id = t.inst_id
and s.saddr = t.ses_addr
order by t.diff desc
/