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;
Recommended Posts
