ORA-01555, snapshot too old errors on transactions

undo tablespace info


select distinct
s.sid,
s.osuser,
s.process,
s.sql_id,
round((u.undoblks*32768) / (1024*1024*1024)) GB,
u.tuned_undoretention
from v$undostat u, v$session s
where u.maxqueryid in s.sql_id
group by
s.sid,
s.osuser,
s.process,
s.sql_id,
u.undoblks,
u.tuned_undoretention
order by s.sid


select distinct
u.maxqueryid,
u.maxquerylen,
u.tuned_undoretention
from v$undostat u, v$session s
where u.tuned_undoretention > 20000 -- <<<-- second retention
and u.maxqueryid not in s.sql_id
group by u.maxqueryid, u.maxquerylen, u.tuned_undoretention;



find the SQL_ID retention period and not in active


select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';




SQL> select sql_text from DBA_HIST_SQLTEXT where sql_id = '0rc4km05kgzb9';

SQL_TEXT
--------------------------------------------------------------------------------
select 1 from obj$ where name='DBA_QUEUE_SCHEDULES'




select s.sid from v$undostat u, v$session s
where u.maxqueryid='0rc4km05kgzb9'
and u.maxqueryid = s.prev_sql_id;


to reset the undo stat


begin
reset_undo_stat();
end;











SYS AS SYSDBA>select max(maxquerylen) from v$undostat;

MAX(MAXQUERYLEN)
----------------
5015

SYS AS SYSDBA>show parameter undo

NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1






if MAX(MAXQUERYLEN)> undo_retention then you will get error



undo_retention =maxquerylen + 300 sec


================
some sql to monitor



set pages 200
col user0 form a15
col comm0 form a15
col name0 form a30
col extents0 form 999 Heading "Extents"
col shrinks0 form 999 Heading "Shrinks"
col waits form 9999 heading "Wraps"

select
rn.name name0,
s.username user0,
r.rssize ,
r.waits,
r.extents extents0,
r.shrinks shrinks0,
r.optsize,
decode (s.command,1,'CREATE TABLE',
2,'INSERT',
3,'SELECT',
6,'UPDATE',
7,'DELETE',
9,'CREATE INDEX',
10,'DROP INDEX',
12,'DROP INDEX',
26,'LOCK TABLE',
44,'COMMIT',
45,'ROLLBACK',
46,'SAVEPOINT',
48,'SET TRANSACTION',
NULL, NULL,
'look it up '||to_char(s.command)) comm0,
sql_id
from v$session s, v$transaction t, v$rollstat r, v$rollname rn
where s.taddr (+) = t.addr
and t.xidusn (+) = r.usn
and rn.usn = r.usn
order by rn.name
/

SELECT rn.name name0
, p.pid
,p.spid
, NVL (p.username, 'NO TRANSACTION') user0
, p.terminal
FROM v$lock l, v$process p, v$rollname rn
WHERE l.sid = p.pid(+)
AND TRUNC (l.id1(+)/65536) = rn.usn
AND l.type(+) = 'TX'
AND l.lmode(+) = 6
ORDER BY rn.name;


PROMPT
PROMPT Time since last WRAP
PROMPT WRAPS The number of times a rollback segment entry has wrapped from one extent to another.

select n.name, round( 24*((sysdate-startup_time) - trunc(sysdate-startup_time)) / (s.writes/s.rssize),1) "Hours"
from v$instance ,v$rollname n,v$rollstat s
where n.usn = s.usn
and s.status = 'ONLINE'
/