ORA-01555 : snapshot too old
One possible reason can be if we leave the cursor open for fetching while we are processing and committing data changes for a long time i,e commit inside a loop
In order to drastically decrease the odds of ORA-01555, commit outside the loop
UNDO_RETENTION : Time period in seconds for which a system retains undo data for committed transactions.
http://blog.oracle48.nl/oracle-database-undo-space-explained/
Optimum retention available with given undo size :
SELECT d.undo_size/(1024*1024) "ACT_UNDO_SIZE [MB]",
SUBSTR(e.value,1,25) " UNDO_RTN [Sec] ",
ROUND((d.undo_size / (to_number(f.value) *
g.undo_block_per_sec))) "OPT_UNDO_RET[Sec]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size' ;
Optimum size for the give retention :
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
Active , Expired and Unexpired blocks :
select tablespace_name,
status,
count(extent_id) "Extent Count",
sum(blocks) "Total Blocks",
sum(blocks)*16/(1024*1024) total_space
from dba_undo_extents
group by tablespace_name, status;
Which session currentl using undo :
select s.sid,
s.username,
decode(round(sum(ss.value) / 1024 / 1024),0,null,round(sum(ss.value) / 1024 / 1024)) as undo_size_mb
from v$sesstat ss
join v$session s on s.sid = ss.sid
join v$statname stat on stat.statistic# = ss.statistic#
where stat.name = 'undo change vector size'
and s.type <> 'BACKGROUND'
and s.username IS NOT NULL group by s.sid, s.username;
select SQL_TEXT from DBA_HIST_SQLTEXT where SQL_ID in
(select distinct MAXQUERYSQLID from
(select * from
(select SNAP_ID,
MAXQUERYLEN,
UNDOBLKS,
MAXQUERYSQLID,
to_char(BEGIN_TIME,'yyyy/mm/dd hh24:mi') begin,
to_char(END_TIME,'yyyy/mm/dd hh24:mi') end
from DBA_HIST_UNDOSTAT
order by UNDOBLKS desc,
MAXQUERYLEN desc
) where rownum<11
)
);
SELECT a.sid, a.username, a.logon_time ,b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC ;
select s.sid,s.serial#,username,s.machine,
t.used_ublk ,t.used_urec,rn.name,(t.used_ublk *16)/1024/1024 SizeGB
from v$transaction t,v$session s,v$rollstat rs, v$rollname rn
where t.addr=s.taddr and rs.usn=rn.usn and rs.usn=t.xidusn and rs.xacts>0;
Undo Change vector :
SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176
ORDER BY a.value DESC ;
http://blog.oracle48.nl/oracle-database-undo-space-explained/
Longest running queries which could have caused ORA-01555
select * from dba_hist_undostat where SSOLDERRCNT >0 ;
