COL MEGAS FORMAT 9G999G999G999
COL EXTENTS FORMAT 999G999
COL TEMPFILE FORMAT A40
COL USERNAME FORMAT A20
BREAK ON TEMPFILE SKIP 1

SET FEED OFF  VERIFY OFF

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

SELECT
  S.TABLESPACE_NAME "Tablespace"
/*  ,S.CURRENT_USERS "ActvUsers"
 ,LPAD( TO_CHAR( S.TOTAL_EXTENTS, 'fm999g999' ), 10, ' ' )  "ExtentsCnt" */
 ,LPAD( TO_CHAR( S.TOTAL_BLOCKS * T.BLOCK_SIZE / 1048576, 'fm999g999' ), 10, ' ' ) "CurrSizeMb"
 ,LPAD( TO_CHAR( S.FREE_BLOCKS * T.BLOCK_SIZE / 1048576, 'fm999g999' ), 10, ' ' ) "CurrFreeMb"
 ,(SELECT LPAD( TO_CHAR( SUM(GREATEST(F.MAXBLOCKS, F.BLOCKS))*T.BLOCK_SIZE/1048576, 'fm999g999' ), 12, ' ' )
   FROM DBA_TEMP_FILES F WHERE F.TABLESPACE_NAME = S.TABLESPACE_NAME) "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 || ' ' ||
  TO_CHAR( S.EXTENT_SIZE * T.BLOCK_SIZE / 1048576, 'fm9g999' ) || 'Mb, '  ||
  T.SEGMENT_SPACE_MANAGEMENT "Extent/Segment Management"
FROM V$SORT_SEGMENT S, DBA_TABLESPACES T
WHERE S.TABLESPACE_NAME = T.TABLESPACE_NAME
/

COL TABLESPACE CLEAR


PROMPT
PROMPT TOP 5 CONSUMERS

SELECT
  (SELECT SID FROM V$SESSION WHERE V.SESSION_ADDR = SADDR ) SID
  , V.SESSION_NUM "SERIAL#"
  , V.USERNAME
  , V.MEGAS
FROM
  (
   SELECT SESSION_ADDR, SESSION_NUM, USERNAME, SUM(BLOCKS) * 8 / 1024 megas
   FROM V$SORT_USAGE
   group by SESSION_ADDR, SESSION_NUM, USERNAME
   order by megas desc
  ) V
WHERE ROWNUM < 6;

PROMPT
PROMPT DETALHES POR TEMPFILE

SET PAGES 300
WITH SU AS
(
  SELECT
    USERNAME, SEGFILE#, SEGRFNO#, SESSION_ADDR,
    SUM(BLOCKS) BLOCKS, SUM(EXTENTS) EXTENTS
  FROM V$SORT_USAGE GROUP BY USERNAME, SEGFILE#, SEGRFNO#, SESSION_ADDR
),
PARAMETRO AS
(
  SELECT TO_NUMBER(VALUE) BLOCKSIZE
  FROM V$PARAMETER WHERE NAME = 'db_block_size'
)
SELECT /*+ALL_ROWS*/
  T.NAME TEMPFILE,U.USERNAME, SUM(S.BLOCKS*P.BLOCKSIZE)/1024/1024 MEGAS, SUM(S.EXTENTS)EXTENTS,
  DECODE(GROUPING(T.NAME)+GROUPING(U.USERNAME),1,'TOTAL DO TEMPFILE', 2, 'TOTAL DO TABLESPACE') TOTAL
FROM
  SU S,
  PARAMETRO P,
  V$SESSION U,
  V$TEMPFILE T
WHERE U.SADDR = S.SESSION_ADDR
AND   T.RFILE# = S.SEGRFNO#
GROUP BY ROLLUP(T.NAME,U.USERNAME)
/

SET PAGES 66
PROMPT
SET FEED 6 VERIFY ON

hostgator