Show usage of rollback segments

SELECT s.sid , s.username , t.used_ublk
FROM v$transaction t
, v$session s
WHERE 1 = 1
AND t.ses_addr = s.saddr;
SELECT s.sid , s.username , t.used_ublk, round((t.used_ublk*8)/1024) size_in_MB_8kb_Block_size, round((t.used_ublk*16)/1024
) size_in_MB_16kb_Block_size
FROM v$transaction t
, v$session s
WHERE 1 = 1
AND t.ses_addr = s.saddr;
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,4) "Size(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');

 

Leave a Reply

Your email address will not be published. Required fields are marked *