SET LINES 134 FEED OFF
COL USN  FORMAT 999                HEAD "USN"
COL NAME FORMAT A11                HEAD "SegName"
COL "Wrp/Srh/Ext" FORMAT A11
COL STATUS FORMAT A7               HEAD "Status"
COL GETS FORMAT 99G999G999         HEAD "Gets"
COL WAITS FORMAT 999G999           HEAD "Waits"
COL XACTS FORMAT 999               HEAD "XActs"
COL EXTENTS FORMAT 99999           HEAD "Extents"
COL MHWMSIZE FORMAT 999G999        HEAD "HWMSizeMb"
COL MRSSIZE FORMAT 999G999         HEAD "RSSizeMb"
COL MWRITES FORMAT 999G999         HEAD "WritesMb"
COL MAVEACTIVE FORMAT 999G999      HEAD "AvgActvMb"

COL MOPTSIZE FORMAT 999G999 NOPRINT

COL "Tablespace"        FORMAT A10
COL "ExtentsCnt"        FORMAT A10
COL "CurrSizeMb"        FORMAT A10
COL "CurrFreeMb"        FORMAT A10
COL "TbsMaxSizeMb"      FORMAT A12
COL "MaxUsedMb"         FORMAT A9
COL "MaxSortMb"         FORMAT A9
COL "Extent/Segment Management" FORMAT A30

WITH filet AS
(
 SELECT
   F.TABLESPACE_NAME
  ,SUM(F.BYTES)/1048576 BYTES
  ,SUM(F.USER_BYTES)/1048576 USERBYTES
  ,SUM(GREATEST(F.BYTES, F.MAXBYTES))/1048576 MAXBYTES
  ,(SELECT SUM(L.BYTES/1048576) FROM DBA_FREE_SPACE L WHERE F.TABLESPACE_NAME = L.TABLESPACE_NAME ) FREEBYTES
 FROM DBA_DATA_FILES F
 WHERE F.TABLESPACE_NAME IN (SELECT TABLESPACE_NAME FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO' )
 GROUP BY F.TABLESPACE_NAME
)
SELECT
  T.TABLESPACE_NAME "Tablespace"
/*  ,S.CURRENT_USERS "ActvUsers"
 ,LPAD( TO_CHAR( S.TOTAL_EXTENTS, 'fm999g999' ), 10, ' ' )  "ExtentsCnt"*/
 ,LPAD( TO_CHAR( F.BYTES, 'fm999g999' ), 10, ' ' ) "CurrSizeMb"
 ,LPAD( TO_CHAR( F.FREEBYTES, 'fm999g999' ), 10, ' ' ) "CurrFreeMb"
 ,LPAD( TO_CHAR( F.MAXBYTES, 'fm999g999' ), 12, ' ' ) "TbsMaxSizeMb"
/*  ,LPAD( TO_CHAR( S.MAX_USED_BLOCKS  * T.BLOCK_SIZE / 1048576, 'fm999g999' ), 9, ' ' ) "MaxUsedMb"
 ,LPAD( TO_CHAR( S.MAX_SORT_BLOCKS * T.BLOCK_SIZE / 1048576, 'fm999g999' ), 9, ' ' ) "MaxSortMb" */
 ,T.EXTENT_MANAGEMENT || ' ' || T.ALLOCATION_TYPE || ' ' ||
  DECODE( T.NEXT_EXTENT, NULL, 'AutoAlloc, ', TO_CHAR( T.NEXT_EXTENT * T.BLOCK_SIZE / 1048576, 'fm9g999' ) || 'Mb, ' ) ||
  T.SEGMENT_SPACE_MANAGEMENT "Extent/Segment Management"
FROM DBA_TABLESPACES T, FILET F
WHERE T.TABLESPACE_NAME = F.TABLESPACE_NAME
/

with blocos as
(
  SELECT SUM(UNDOBLKS) TOTAL, SUM(ACTIVEBLKS) ATIVOS, SUM(UNEXPIREDBLKS) UNEXPIRED, SUM(EXPIREDBLKS) EXPIRED
  FROM V$UNDOSTAT
),
prop as
(
  SELECT MAX(BLOCK_SIZE) BLOCK_SIZE, 1048576 MEGA FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO'
)
SELECT
  /* trunc(b.total * p.block_size / p.mega ) "Total"
 , */trunc(b.ativos * p.block_size / p.mega ) "Ativo"
 ,trunc(b.unexpired * p.block_size / p.mega ) "Não Expirado"
 ,trunc(b.expired * p.block_size / p.mega ) "Expirado"
FROM BLOCOS B
CROSS JOIN PROP P
/

select n.usn, n.name, s.xacts, s.extents,
       lpad(s.wraps,3,' ')||'|'||lpad(s.shrinks,3,' ')||'|'||lpad(s.extends,3,' ') "Wrp/Srh/Ext",
       s.gets, s.waits,
       status,
       trunc(s.aveactive/1048576) maveactive,
       trunc(s.rssize/1048576) mrssize,
       trunc(s.hwmsize/1048576) mhwmsize,
       trunc(s.writes/1048576) mwrites,
       trunc(s.optsize/1048576) moptsize
from v$rollstat s, v$rollname n
where n.usn = s.usn
order by n.USN
/
PROMPT
SET FEED 6
COL MOPTSIZE CLEAR