create or replace view undo_user_usage as (
SELECT V.SID sid, V.SERIAL# serial#, V.USERNAME username, sum(E.BYTES)/1024/1024 mb
FROM V$SESSION V, V$TRANSACTION T, DBA_ROLLBACK_SEGS R, dba_undo_extents e
WHERE V.SADDR=T.SES_ADDR AND T.XIDUSN=R.SEGMENT_ID AND R.TABLESPACE_NAME in
(select tablespace_name from dba_tablespaces where contents='UNDO')
and e.status='ACTIVE'
group by sid, serial#, username )
);
select nvl(max(mb),0) from user_undo_usage;
SQL> desc dba_undo_extents;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER CHAR(3)
SEGMENT_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME NOT NULL VARCHAR2(30)
EXTENT_ID NUMBER
FILE_ID NOT NULL NUMBER
BLOCK_ID NUMBER
BYTES NUMBER
BLOCKS NUMBER
RELATIVE_FNO NUMBER
COMMIT_JTIME NUMBER
COMMIT_WTIME VARCHAR2(20)
STATUS VARCHAR2(9)
SQL> select text from dba_views where view_name='DBA_UNDO_EXTENTS';
TEXT
--------------------------------------------------------------------------------
select /*+ ordered use_nl(e) use_nl(f) */
'SYS', --OWNER
u.name, --SEGMENT_NAME
t.name, --TABLESPACE_NAME
e.ktfbueextno, --EXTENT_ID
f.file#, --FILE_ID
e.ktfbuebno, --BLOCK_ID
e.ktfbueblks * t.BLOCKSIZE, --BYTES
e.ktfbueblks, --BLOCKS
e.ktfbuefno, --RELATIVE_FNO
nullif(e.ktfbuectm, e.ktfbuectm), --COMMIT_JTIME
nullif(e.ktfbuestt, e.ktfbuestt), --COMMIT_WTIME
decode(e.ktfbuesta, 1, 'ACTIVE', 2, 'EXPIRED', 3, 'UNEXPIRED','UNDEFINED') --STATUS
from undo$ u, ts$ t, sys.x$ktfbue e, sys.file$ f
where
e.ktfbuesegfno = u.file#
and e.ktfbuesegbno = u.block#
and e.ktfbuesegtsn = u.ts#
and u.spare1 = 1
and t.ts# = u.ts#
and e.ktfbuefno = f.relfile#
and u.ts# = f.ts#
