TABLESPACE USAGE

Посмотреть, как табличные пространства связаны с файлами данных можно так:
(DBA_FREE_SPACE, позволяет определить, сколько свободного места осталось в нашем табличном пространстве.) 


BREAK ON tablespace_name SKIP 2
COMPUTE SUM OF allocated_bytes, free_bytes ON tablespace_name
COLUMN allocated_bytes FORMAT 9,999,999,999
COLUMN free_bytes FORMAT 9,999,999,999

SELECT a.tablespace_name,
       a.file_name,
       a.bytes allocated_bytes,
       b.free_bytes
 FROM dba_data_files a,
     (SELECT file_id, SUM(bytes) free_bytes
      FROM dba_free_space b GROUP BY file_id) b
WHERE a.file_id=b.file_id
ORDER BY a.tablespace_name;



Информацию об использовании пространства в ТП
без учета autoextensible можно посмотреть так:

SELECT /* + RULE */
 df.tablespace_name "Tablespace",
 df.bytes / (1024 * 1024) "Size (MB)",
 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes), 1) "% Free",
 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
  FROM dba_free_space fs,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name(+) = df.tablespace_name
 GROUP BY df.tablespace_name, df.bytes
UNION ALL
SELECT /* + RULE */
 df.tablespace_name tspace,
 fs.bytes / (1024 * 1024),
 SUM(df.bytes_free) / (1024 * 1024),
 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
  FROM dba_temp_files fs,
       (SELECT tablespace_name, bytes_free, bytes_used
          FROM v$temp_space_header
         GROUP BY tablespace_name, bytes_free, bytes_used) df
 WHERE fs.tablespace_name(+) = df.tablespace_name
 GROUP BY df.tablespace_name, fs.bytes, df.bytes_free, df.bytes_used
 ORDER BY 5 DESC;

Или так:

SELECT /*+ first_rows */ d.tablespace_name "TS NAME",
NVL(a.bytes / 1024 / 1024, 0) "size MB",
NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024 "Used MB",
NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) "Used %",
a.autoext "Autoextend",
NVL(f.bytes, 0) / 1024 / 1024 "Free MB",
d.status "STAT",
a.count "# of datafiles",
d.contents "TS type",
d.extent_management "EXT MGMT",
d.segment_space_management "Seg Space MGMT"
FROM sys.dba_tablespaces d,
    
     (select tablespace_name,
             sum(bytes) bytes,
             count(file_id) count,
             decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
       from dba_data_files group by tablespace_name) a,
    
     (select tablespace_name,
             sum(bytes) bytes
       from dba_free_space
       group by tablespace_name) f
       WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT d.contents = 'UNDO'
       AND NOT (d.extent_management = 'LOCAL'
       AND d.contents = 'TEMPORARY')
       AND d.tablespace_name like '%%'
    
       UNION ALL
    
       SELECT d.tablespace_name, NVL(a.bytes / 1024 / 1024, 0),
       NVL(t.bytes, 0)/1024/1024, NVL(t.bytes / a.bytes * 100, 0),
       a.autoext,
       (NVL(a.bytes ,0)/1024/1024 - NVL(t.bytes, 0)/1024/1024),
       d.status,
       a.count,
       d.contents,
       d.extent_management,
       d.segment_space_management
       FROM sys.dba_tablespaces d,
            (select tablespace_name,
                    sum(bytes) bytes,
                    count(file_id) count,
                    decode(sum(decode(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
                    from dba_temp_files
             group by tablespace_name) a,
             (select ss.tablespace_name ,
                     sum((ss.used_blocks*ts.blocksize)) bytes
                     from gv$sort_segment ss, sys.ts$ ts
                     where ss.tablespace_name = ts.name
                     group by ss.tablespace_name) t
                     WHERE d.tablespace_name = a.tablespace_name(+)
                     AND d.tablespace_name = t.tablespace_name(+)
                     AND d.extent_management = 'LOCAL'
                     AND d.contents = 'TEMPORARY'
                     and d.tablespace_name like '%%'
             
              UNION ALL
             
              SELECT d.tablespace_name,
                            NVL(a.bytes / 1024 / 1024, 0),
                            NVL(u.bytes, 0) / 1024 / 1024,
                            NVL(u.bytes / a.bytes * 100, 0),
                            a.autoext,
                            NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024,
                            d.status,
                            a.count,
                            d.contents,
                            d.extent_management,
                            d.segment_space_management
                         FROM sys.dba_tablespaces d,
                              (SELECT tablespace_name, SUM(bytes) bytes,
                               COUNT(file_id) count,
                               decode(sum(decode(autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
                                 FROM dba_data_files
                                 GROUP BY tablespace_name) a, (SELECT tablespace_name, SUM(bytes) bytes
                                                                  FROM (SELECT tablespace_name,sum (bytes) bytes,status
                                                                        from dba_undo_extents
                                                                        WHERE status ='ACTIVE'
                                                                        group by tablespace_name,status

UNION ALL

SELECT tablespace_name,
sum(bytes) bytes,
status from dba_undo_extents
WHERE status ='UNEXPIRED'
group by tablespace_name,status )

group by tablespace_name ) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.contents = 'UNDO'
AND d.tablespace_name LIKE '%%' ORDER BY 1

Учесть autoextensible можно так:

set lines 132
set pages 105
set pause off
set echo off
set feedb on

column "TOTAL ALLOC (MB)"      format 9,999,990.00
column "TOTAL PHYS ALLOC (MB)" format 9,999,990.00
column "USED (MB)"             format 9,999,990.00
column "FREE (MB)"             format 9,999,990.00
column "% USED"                format 990.00

select
   a.tablespace_name,
   a.bytes_alloc/(1024*1024) "TOTAL ALLOC (MB)",
   a.physical_bytes/(1024*1024) "TOTAL PHYS ALLOC (MB)",
   nvl(b.tot_used,0)/(1024*1024) "USED (MB)",
   (nvl(b.tot_used,0)/a.bytes_alloc)*100 "% USED"
from
   (select
      tablespace_name,
      sum(bytes) physical_bytes,
      sum(decode(autoextensible,'NO',bytes,'YES',maxbytes)) bytes_alloc
    from
      dba_data_files
    group by
      tablespace_name ) a,
   (select
      tablespace_name,
      sum(bytes) tot_used
    from
      dba_segments
    group by
      tablespace_name ) b
where
   a.tablespace_name = b.tablespace_name (+)
and
   a.tablespace_name not in
   (select distinct
       tablespace_name
    from
       dba_temp_files)
and
   a.tablespace_name not like 'UNDO%'
order by 1;

Или так:

set pages 999
set lines 400

SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM dba_free_space fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_data_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
UNION ALL
SELECT df.tablespace_name tablespace_name,
 max(df.autoextensible) auto_ext,
 round(df.maxbytes / (1024 * 1024), 2) max_ts_size,
 round((df.bytes - sum(fs.bytes)) / (df.maxbytes) * 100, 2) max_ts_pct_used,
 round(df.bytes / (1024 * 1024), 2) curr_ts_size,
 round((df.bytes - sum(fs.bytes)) / (1024 * 1024), 2) used_ts_size,
 round((df.bytes-sum(fs.bytes)) * 100 / df.bytes, 2) ts_pct_used,
 round(sum(fs.bytes) / (1024 * 1024), 2) free_ts_size,
 nvl(round(sum(fs.bytes) * 100 / df.bytes), 2) ts_pct_free
FROM (select tablespace_name, bytes_used bytes
 from V$temp_space_header
 group by tablespace_name, bytes_free, bytes_used) fs,
 (select tablespace_name,
 sum(bytes) bytes,
 sum(decode(maxbytes, 0, bytes, maxbytes)) maxbytes,
 max(autoextensible) autoextensible
 from dba_temp_files
 group by tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name, df.bytes, df.maxbytes
ORDER BY 4 DESC;



Иногда может наблюдаться замедление работы запросов при обращении к DBA_FREE_SPACE, а также появление чрезмерного ожидания операций ввода-вывода.
Для решения этой проблемы нужно:

Удалить содержимое корзины

connect / as sysdba
select count(*) from dba_recyclebin;
purge dba_recyclebin;

Собрать статистику для X$KTFBUE

connect / as sysdba
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'SYS', TABNAME => 'X$KTFBUE',ESTIMATE_PERCENT=>100);

Проверяем:

column owner format a6
column table_name format a10
column last_anal format a10
SELECT owner,  table_name,  last_analyzed FROM dba_tab_statistics WHERE table_name='X$KTFBUE';

Начиная с версии 11g можно воспользоваться следующим представлением:

Размер табличных пространств в блоках
select * from DBA_TABLESPACE_USAGE_METRICS;

Переводим блоки в Гигабайты

SELECT tbm.TABLESPACE_NAME,
   round(tbm.USED_SPACE * tb.BLOCK_SIZE /(1024*1024*1024),2) USED_SPACE_GB,
   round(tbm.TABLESPACE_SIZE * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_SIZE_GB,
   round((tbm.TABLESPACE_SIZE - tbm.USED_SPACE) * tb.BLOCK_SIZE /(1024*1024*1024),2) TABLESPACE_FREE_SIZE_GB,
   tbm.USED_PERCENT
FROM dba_tablespace_usage_metrics tbm
     join dba_tablespaces tb on tb.TABLESPACE_NAME = tbm.TABLESPACE_NAME
--WHERE tbm.tablespace_name = 'tablespace_name'

В мегабайтах

SELECT a.tablespace_name,
  ROUND((a.used_space * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
  ROUND((a.tablespace_size * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
  ROUND(a.used_percent, 2) AS "USED_PERCENT"
  FROM DBA_TABLESPACE_USAGE_METRICS a JOIN
  DBA_TABLESPACES b
  ON a.tablespace_name = b.tablespace_name;

Можно связать с представлением dba_tablespace_thresholds:

Для версии 11gR1:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1) USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,   
    count(distinct d.file_id) DBF_NO,
    max(to_number(tt.warning_value)) WARN,
    max(to_number(tt.critical_value)) CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m,
      dba_tablespaces t,
      dba_data_files d,
      dba_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
  AND d.tablespace_name=t.tablespace_name
  AND tt.metrics_name='Tablespace Space Usage'
  AND tt.object_name is null
GROUP BY m.tablespace_name
ORDER BY 2 desc;


Для версии 11gR2 и выше:

SELECT m.tablespace_name,
    round(max(m.used_percent),1) PERCM,
    round(max(m.used_space*t.block_size)*100/(sum(d.bytes)*count(distinct d.file_id)/count(d.file_id)),1) PERC,
    round(max(m.tablespace_size*t.block_size/1024/1024),1) TOTALM,
    round((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)/1024/1024,1) TOTAL,
    round(max(m.used_space*t.block_size/1024/1024),1) USED,
    round(max((m.tablespace_size-m.used_space)*t.block_size/1024/1024),1) FREEM,
    round(((sum(d.bytes)*count(distinct d.file_id))/count(d.file_id)-max(m.used_space*t.block_size))/1024/1024,1) FREE,    
    count(distinct d.file_id) DBF_NO,
    max(to_number(tt.warning_value)) WARN,
    max(to_number(tt.critical_value)) CRIT,
    max(case when m.used_percent>tt.warning_value OR m.used_percent>tt.critical_value then 'NO!' else 'OK' end) "OK?"
FROM  dba_tablespace_usage_metrics m,
      dba_tablespaces t,
      dba_data_files d,
      dba_tablespace_thresholds tt
WHERE m.tablespace_name=t.tablespace_name
  AND d.tablespace_name=t.tablespace_name
  AND tt.tablespace_name=d.tablespace_name
  AND tt.metrics_name='Tablespace Space Usage'
GROUP BY m.tablespace_name
ORDER BY 2 desc;

Исторические данные по росту ТП из AWR

В блоках:

select TABLESPACE_ID, NAME, TABLESPACE_SIZE, TABLESPACE_USEDSIZE, RTIME
from DBA_HIST_TBSPC_SPACE_USAGE, v$tablespace
where TABLESPACE_ID=TS#
order by RTIME, dba_hist_tbspc_space_usage.tablespace_id;

В мегабайтах:

select a.TABLESPACE_ID,
       c.NAME,
       ROUND((a.TABLESPACE_USEDSIZE * b.block_size)/1024/1024, 2) AS "USED_SPACE(MB)",
       ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2) AS "TABLESPACE_SIZE(MB)",
       a.RTIME
from   DBA_HIST_TBSPC_SPACE_USAGE a,
       DBA_TABLESPACES b,
       v$tablespace c
where  a.TABLESPACE_ID=c.TS#
   and c.name = b.tablespace_name
order by a.RTIME, a.tablespace_id;

Исторические данные роста базы данных:

select SUM(ROUND((a.TABLESPACE_SIZE * b.block_size)/1024/1024, 2)) AS "DATABASE_SIZE(MB)",
       a.RTIME
from   DBA_HIST_TBSPC_SPACE_USAGE a,
       DBA_TABLESPACES b,
       v$tablespace c
where  a.TABLESPACE_ID=c.TS#
   and c.name = b.tablespace_name
group by a.RTIME
order by a.RTIME;

Только это будет без  redo_size from sys.v_$log  и controlfile_size from v$controlfile





А общий размер базы будет таким:

select
( select sum(bytes)/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size from v$controlfile) "Size in MB"
from
dual


Или в гигабайтах:

select
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +
( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"
from
dual

Создание табличных пространств:

Файловая система:

create tablespace TEST
 datafile '/u02/oradata/TESTDB/test_01.dbf' size 128M autoextend on next 128M maxsize unlimited,
          '/u02/oradata/TESTDB/test_02.dbf' size 128M autoextend on next 128M maxsize unlimited,
          '/u02/oradata/TESTDB/test_03.dbf' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


ASM:

create tablespace TEST
 datafile  '+DATA' size 128M autoextend on next 128M maxsize unlimited,
           '+DATA' size 128M autoextend on next 128M maxsize unlimited,
           '+DATA' size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;


OMF:

create tablespace TEST
 datafile  size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited,
           size 128M autoextend on next 128M maxsize unlimited
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

Просмотр информации о табличном пространстве:

select
        file_name "Filename",
        tablespace_name "Tablespace",
        round(bytes/1024/1024 ,2) "Current Size (MB)",
        autoextensible "Autoextend",
        round(increment_by*8192/1024/1024 ,2) "Autoextend Size (MB)",
        round(maxbytes/1024/1024 ,2) "Max Size (MB)"
from dba_data_files
where tablespace_name = 'TEST';

Добавление файлов данных в табличное пространство:

Файловая система:

alter tablespace TEST add datafile '/u02/oradata/TESTDB/test_04.dbf' size 128M autoextend on next 128M maxsize unlimited;


ASM:

alter tablespace TEST add datafile '+DATA' size 128M autoextend on next 128M maxsize unlimited;


OMF:

alter tablespace TEST add datafile size 128M autoextend on next 128M maxsize unlimited;

Удаление файлов данных из табличных пространств:

Файловая система:
alter tablespace TEST drop datafile '/u02/oradata/TESTDB/test_04.dbf'


ASM:

alter tablespace TEST drop datafile '+DATA/test_df4.f';


OMF:

alter tablespace TEST drop datafile 'C:\APP\ORACLE\ORADATA\TESTDB\DATAFILE\O1_MF_TEST_FC98Q9LO_.DBF';

Удаление табличных пространств:

drop tablespace TEST;
drop tablespace TEST including contents and datafiles;

Подробная информация о табличных пространствах:

set lines 556
set trimout on
set space 1
set tab off

set pagesize 10000 linesize 300 tab off

col tablespace_name format A22              heading "Tablespace"
col ts_type         format A13              heading "TS Type"
col segments        format 999999           heading "Segments"
col files           format 9999
col allocated_mb    format 9,999,990.000    heading "Allocated Size|(Mb)"
col used_mb         format 9,999,990.000    heading "Used Space|(Mb)"
col Free_mb         format 999,990.000      heading "Free Space|(Mb)"
col used_pct        format 999              heading "Used|%"
col max_ext_mb      format 99,999,990.000   heading "Max Size|(Mb)"
col max_free_mb     format 9,999,990.000    heading "Max Free|(Mb)"
col max_used_pct    format 999              heading "Max Used|(%)"

BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE   ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT

WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
     tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
     tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
     ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt files, 
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,3) Used_MB,
       ROUND(NVL(f.bytes, 0) / 1024 / 1024, 3) Free_MB,
       ROUND(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_ext_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND d.tablespace_name = m.tablespace_name(+)
   AND d.tablespace_name = s.tablespace_name(+)
   AND NOT d.contents = 'UNDO'
   AND NOT ( d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY' )
UNION ALL
-- TEMP TS
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt,
       0,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
       ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
       ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
  FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.tablespace_name = m.tablespace_name(+)
   AND d.extent_management = 'LOCAL'
   AND d.contents = 'TEMPORARY' 
UNION ALL
-- UNDO TS
SELECT d.tablespace_name,
       d.status,
       DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
       a.cnt,
       NVL(s.segcnt,0) segments,
       ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
       ROUND(NVL(u.bytes, 0) / 1024 / 1024, 3) Used_MB,
       ROUND(NVL(a.bytes - NVL(u.bytes, 0), 0)/1024/1024, 3) Free_MB,
       ROUND(NVL(u.bytes / a.bytes * 100, 0), 2) Used_pct,
       ROUND(a.maxbytes / 1024 / 1024, 3)  max_size_mb,
       ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, df a, tm m, ts s, (SELECT tablespace_name, SUM(bytes) bytes FROM dba_undo_extents where status in ('ACTIVE','UNEXPIRED') GROUP BY tablespace_name) u
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = u.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.tablespace_name = s.tablespace_name(+)
AND d.contents = 'UNDO'
ORDER BY 1
/

prompt * Tablespace (TS) types:
prompt .  - LM/DM     - Local/Dictionary Managed
prompt .  - SYS/UNI   - SYStem/UNIform Extent Management (LM only)
prompt .  - ASSM/MSSM - Automatic/Manual Segment Space Management (ASSM -> LM only)