SET TERMOUT OFF
COL PRINT_DETALHE NEW_VALUE PRINT_DETALHE
COL BREAK_DETALHE NEW_VALUE BREAK_DETALHE

SELECT
  DECODE( UPPER('&DETALHAR.'), 'SIM', 'PRINT', 'NOPRINT' ) PRINT_DETALHE
 ,DECODE( UPPER('&DETALHAR.'), 'SIM', 'ON TABLESPACE_NAME SKIP PAGE', 'ON REPORT SKIP PAGE' ) BREAK_DETALHE
FROM DUAL
/

SET TERMOUT ON

BREAK &BREAK_DETALHE.
COMPUTE SUM LABEL "TOTAIS" OF MAX_MB SIZE_MB FREE_MB ON REPORT
SET VERIFY OFF DEFINE ON LINES 189 NUMWIDTH 7 FEED OFF PAGES 200
COL TABLESPACE_NAME FORMAT A30
COL FILE_NAME FORMAT A60
COL DETALHE FORMAT A21 &PRINT_DETALHE.
COL TIPO FORMAT A34
COL STATUS FORMAT A9
COL FILE_ID FORMAT 9999 HEAD "F#"
COL PCT_USED_MAX FORMAT A12
COL PCT_USED FORMAT A8

SELECT
  V.TABLESPACE_NAME
 ,V.STATUS
 ,V.TIPO
 ,V.MAX_MB
 ,V.SIZE_MB
 ,NVL( V.FREE_MB, 0 ) FREE_MB
 ,TO_CHAR( ROUND((V.SIZE_MB-NVL(V.FREE_MB,0))/NULLIF(V.MAX_MB,0)*100, 2 ), '99999990D00' ) PCT_USED_MAX
 ,TO_CHAR(ROUND((V.SIZE_MB-NVL(V.FREE_MB,0))/NULLIF(V.SIZE_MB,0)*100, 2 ), '9990D00' ) PCT_USED
/*
 ,TRUNC(FREE_MB/1&zero.)*1&zero. "1&zero._MB"
 ,TRUNC(FREE_MB/2&zero.)*2&zero. "2&zero._MB"
 ,TRUNC(FREE_MB/3&zero.)*3&zero. "3&zero._MB"
 ,TRUNC(FREE_MB/4&zero.)*4&zero. "4&zero._MB"
 ,TRUNC(FREE_MB/5&zero.)*5&zero. "5&zero._MB"
 ,TRUNC(FREE_MB/10&zero.)*10&zero. "10&zero._MB"
 ,TRUNC(FREE_MB/20&zero.)*20&zero. "20&zero._MB"
*/
 ,CURSOR ( SELECT FILE_ID, FILE_NAME, MEGAS, USERMEGAS, ROUND( INCREMENTO * V.BLOCK_SIZE / 1048576, 2 ) NEXTMEGAS, MAXMEGAS
           FROM (
             SELECT TABLESPACE_NAME, FILE_NAME, file_id,
                    SUBSTR( FILE_NAME, INSTR(FILE_NAME, '/', -1 ) +1 ),
                    ROUND(BYTES/1048576, 2 ) MEGAS,
                    ROUND(MAXBYTES/1048576, 2 ) MAXMEGAS,
                    INCREMENT_BY INCREMENTO,
                    ROUND(USER_BYTES/1048576, 2 ) USERMEGAS
             FROM DBA_DATA_FILES
             UNION ALL
             SELECT TABLESPACE_NAME, FILE_NAME, file_id,
                    SUBSTR( FILE_NAME, INSTR(FILE_NAME, '/', -1 ) +1 ),
                    ROUND(BYTES/1048576, 2 ) MEGAS,
                    ROUND(MAXBYTES/1048576, 2 ) MAXMEGAS,
                    INCREMENT_BY INCREMENTO,
                    ROUND(USER_BYTES/1048576, 2 ) USERMEGAS
             FROM DBA_TEMP_FILES
             ORDER BY 3 DESC
           ) C
           WHERE C.TABLESPACE_NAME = V.TABLESPACE_NAME AND ROWNUM <= 45 ) DETALHE
FROM
(
  SELECT TBS.TABLESPACE_NAME,
         DECODE( EXTENT_MANAGEMENT, 'DICTIONARY', EXTENT_MANAGEMENT,
                 EXTENT_MANAGEMENT || ' ' || DECODE( ALLOCATION_TYPE, 'SYSTEM', 'AUTOALLOCATE', 'UNIFORM' ) ) || ' ' ||
                 SUBSTR( CONTENTS, 1, 4 ) || DECODE( LOGGING, 'LOGGING', ' LOGG', ' NOLOG' ) || ' ' ||
                 DECODE( SEGMENT_SPACE_MANAGEMENT, 'AUTO', 'ASSM' ) TIPO
  , STATUS
  , BLOCK_SIZE
  , ( SELECT SUM(TRUNC(DECODE(I.INCREMENT_BY, 0, I.BYTES, I.MAXBYTES)/1048576))
      FROM ( SELECT tablespace_name, increment_by, bytes, maxbytes FROM DBA_DATA_FILES F
             UNION ALL
             SELECT tablespace_name, increment_by, bytes, maxbytes FROM DBA_TEMP_FILES T ) I
      WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME )
      "MAX_MB"
  , ( SELECT SUM(TRUNC(I.BYTES/1048576))
      FROM ( SELECT tablespace_name, increment_by, bytes, maxbytes FROM DBA_DATA_FILES F
             UNION ALL
             SELECT tablespace_name, increment_by, bytes, maxbytes FROM DBA_TEMP_FILES T ) I
      WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME )
      "SIZE_MB"
  , CASE TBS.CONTENTS
       WHEN 'TEMPORARY' THEN
         ( SELECT
            TRUNC(
              ((SELECT SUM(T.BYTES) FROM DBA_TEMP_FILES T WHERE T.TABLESPACE_NAME = TBS.TABLESPACE_NAME) -
                 (SELECT SUM(I.BLOCKS*TBS.BLOCK_SIZE) FROM V$SORT_USAGE I WHERE I.TABLESPACE = TBS.TABLESPACE_NAME))/1048576
             )
           FROM DUAL
         )
       ELSE
         ( SELECT SUM(TRUNC(I.BYTES/1048576))
           FROM DBA_FREE_SPACE I
           WHERE I.TABLESPACE_NAME = TBS.TABLESPACE_NAME
         )
    END "FREE_MB"
  FROM DBA_TABLESPACES TBS
  WHERE TBS.TABLESPACE_NAME LIKE UPPER('&tbs.')
) V
--ORDER BY /*PCT_USED  DESC */ FREE_MB 
ORDER BY TO_NUMBER(PCT_USED_MAX)  DESC
/
PROMPT

UNDEFINE 1 2 3 PRINT_DETALHE BREAK_DETALHE
SET VERIFY ON NUMWIDTH 10 FEED 6 PAGES 66

CLEAR COMPUTE
CLEAR BREAK

COL TABLESPACE_NAME CLEAR
COL DETALHE CLEAR
COL TIPO CLEAR
COL STATUS CLEAR