Total Undo
**************
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;
OR
select file_name, bytes/1024/1024/1024 "bytes in_gb", autoextensible, maxbytes/1024/1024/1024 "maxbytes in_gb"
from dba_data_files where tablespace_name='UNDOTBS1';
Undo Datafile Information
****************************
select dt.tablespace_name, dt.contents, ddf.file_name, ddf.bytes/1024/1024/1024 size_gb
from dba_tablespaces dt, dba_data_files ddf
where dt.tablespace_name = ddf.tablespace_name
and dt.contents like 'UNDO';
Total Usage of Undo
***************************
select
( select sum(bytes)/1024/1024 from dba_data_files
where tablespace_name like 'UND%' ) undo_allocated_MB,
( select sum(bytes)/1024/1024 from dba_free_space
where tablespace_name like 'UND%') undo_free_MB,
( select sum(bytes)/1024/1024 from dba_undo_extents
where tablespace_name like 'UND%') undo_used_MB
from dual;
OR
select tablespace_name, sum(blocks) * 8192/1024/1024 "undo_usage_in_MB"
from dba_undo_extents
group by tablespace_name;
Undo usage detailed info
****************************
select tablespace_name, status, sum(blocks) * 8192/1024/1024 size_in_MB,
count(*) as "HOW_MANY_txtns"
from dba_undo_extents
group by tablespace_name ,status;
Who Is Using Undo & How Much
******************************************
select ses.username , substr(ses.program, 1, 19) program,
tra.used_ublk
from v$session ses, v$transaction tra
where ses.saddr = tra.ses_addr;
OR
select to_char (s.sid) || ',' || to_char (s.serial#) sid_serial,
nvl (s.username, 'none') orauser,osuser, s.program, r.name undoseg,
t.used_ublk * to_number (x.value) / 1024 || 'k' "undo",
t1.tablespace_name
from sys.v_$rollname r, sys.v_$session s,
sys.v_$transaction t, sys.v_$parameter x, dba_rollback_segs t1
where s.taddr = t.addr
and r.usn = t.xidusn(+)
and x.name = 'db_block_size'
and t1.segment_id = r.usn
and t1.tablespace_name = 'UNDOTBS1';
OR
select a.name,b.status ,d.osuser ,d.username , d.sid , d.serial#
from v$rollname a,v$rollstat b, v$transaction c , v$session d
where a.usn = b.usn
and a.usn = c.xidusn
and c.ses_addr = d.saddr
and a.name in ( select segment_name
from dba_segments
where tablespace_name = 'UNDOTBS1);
Undo Using Sess Info
*********************************
select s.sid , s.username ,t.status,t.start_time,t.noundo,t.ptx,t.name, t.used_ublk
from v$transaction t , v$session s
where 1 = 1
and t.ses_addr = s.saddr;
OR
select distinct rpad(s.sid,3) "sid",s.username,
e.segment_name,
t.start_time "start",
rpad(t.status,9) "status",
round((t.used_ublk*8)/1024) "size used(mb)"
--t.used_ublk||' blocks and '||t.used_urec||' records' "rollback usage"
from dba_data_files df,
dba_extents e,
v$session s,
v$transaction t
where df.tablespace_name = e.tablespace_name and
df.file_id = ubafil and
s.saddr = t.ses_addr and
t.ubablk between e.block_id and e.block_id+e.blocks and
e.segment_type in( 'ROLLBACK','TYPE2 UNDO');
Find The Active Txns Using Undo
*******************************************
select s.username,s.osuser,s.sid,s.serial#, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
Pending & Active Txns
**********************************
select a.name,b.status
from v$rollname a,v$rollstat b
where a.usn = b.usn
and a.name in ( select segment_name
from dba_segments
where tablespace_name = 'UNDOTBS1' );
Note: If pending txns or active txns are there we cannot drop that tablespaces.
Suggestion for undo size needed ......using Advisory:
***********************************************************
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 upper(value) from v$parameter where name = 'undo_tablespace'));
