Undo tablespace

1. Check who hold undo tablespace

select rtrim(r.name), l.sid, nvl(s.username,'No Txn') usern,s.MACHINE,s.OSUSER,s.PROGRAM,s.USERNAME,to_char(s.LOGON_TIME,'YYYY/MM/DD hh24:MI:SS') logon_time, s.terminal,rs.RSSIZE
from v$lock l, v$session s, v$rollname r, v$rollstat rs
where trunc((l.id1) / 65536 ) =r.usn
and l.type(+) = 'TX'
and l.lmode(+) = 6
and s.sid = l.sid
and r.usn=rs.USN
order by r.name;

2. -- Check why ORA-01555 happen , the time period need to adjustment.
select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT "# Unexpired|Stolen", EXPSTEALCNT "# Expired|Reused", SSOLDERRCNT "ORA-1555|Error", NOSPACEERRCNT "Out-Of-space|Error", MAXQUERYLEN "Max Query|Length"
from gv$undostat
where begin_time between to_date('01/01/2008 01:00:00','MM/DD/YYYY HH24:MI:SS') and to_date('01/30/2008 01:00:00','MM/DD/YYYY HH24:MI:SS')
order by inst_id, begin_time;

3. -- Estimate undo size
SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),
(select block_size as DBS from dba_tablespaces where tablespace_name=
(select value from v$parameter where name = 'undo_tablespace'));

4. -- max long query in db history
select max(maxquerylen) from v$undostat;