Important Queries Related to Undo in Oracle

To find retention guarantee for undo tablespace

select tablespace_name,status,contents,retention from dba_tablespaces where tablespace_name like '%UNDO%';

To find Undo Space Availability

select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;

To show ACTIVE/EXPIRED/UNEXPIRED Extents of Undo Tablespace

select status,

  round(sum_bytes / (1024*1024), 0) as MB,

  round((sum_bytes / undo_size) * 100, 0) as PERC

from

(

  select status, sum(bytes) sum_bytes

  from dba_undo_extents

  group by status

),

(

  select sum(a.bytes) undo_size

  from dba_tablespaces c

    join v$tablespace b on b.name = c.tablespace_name

    join v$datafile a on a.ts# = b.ts#

  where c.contents = 'UNDO'

    and c.status = 'ONLINE'

);

To find Undo Retention Value

show parameter undo_retention;

Undo retention in hours

col "Retention" for a30

col name for a30

col value for a50

select name "Retention",value/60/60 "Hours" from v$parameter where name like '%undo_retention%';

Session Details Consuming Undo

SELECT s.inst_id,

        r.name                   rbs,

        nvl(s.username, 'None')  oracle_user,

        s.osuser                 client_user,

        p.username               unix_user,

        to_char(s.sid)||','||to_char(s.serial#) as sid_serial,

        p.spid                   unix_pid,

        TO_CHAR(s.logon_time, 'mm/dd/yy hh24:mi:ss') as login_time,

        t.used_ublk * 8192  as undo_BYTES,

                st.sql_text as sql_text

   FROM gv$process     p,

        v$rollname     r,

        gv$session     s,

        gv$transaction t,

        gv$sqlarea     st

  WHERE p.inst_id=s.inst_id

    AND p.inst_id=t.inst_id

    AND s.inst_id=st.inst_id

    AND s.taddr = t.addr

    AND s.paddr = p.addr(+)

    AND r.usn   = t.xidusn(+)

    AND s.sql_address = st.address

  AND t.used_ublk * 8192 > 1073741824

  ORDER

       BY undo_BYTES desc

/

To check space related statistics of  UndoTablespace from v$undostat

select

sum(ssolderrcnt) "Total ORA-1555s",

round(max(maxquerylen)/60/60) "Max Query HRS",

sum(unxpstealcnt) "UNExpired STEALS",

sum(expstealcnt) "Expired STEALS"

from v$undostat

order by begin_time;

Find Undo Segments related to users

select  s.sid,s.serial#,username,s.machine,

t.used_ublk ,t.used_urec,(rs.rssize)/1024/1024 MB,rn.name

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;