Oracle 列出表空间和使用情况

如何通过显示可用空间，使用的空间，状态和类型来列出ORCL数据库中的所有表空间。还有与单独查询中的那些表空间相关的永久和临时文件。

这是我过去用过的一个

select d.tablespace_name, lpad(round(d.tot_size/1024/1024)||'m',10) tot_size, lpad(round(f.tot_free/1024/1024)||'m',10) tot_free, round(100-100*tot_free/tot_size) pct_used
from
( select tablespace_name, sum(tot_free) tot_free
  from
  ( select tablespace_name, sum(bytes) tot_free
    from dba_free_space
    group by tablespace_name
    union all
    select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes)-bytes else 0 end )
    from dba_data_files
    group by tablespace_name
    union all
    select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
    from dba_temp_files
    group by tablespace_name
    union all
    select s.tablespace, -1*alloc*t.block_size
    from ( select /*+ NO_MERGE */ tablespace, sum(blocks) alloc
           from   v$sort_usage
           group by tablespace) s,
          dba_tablespaces t
    where t.tablespace_name = s.tablespace
  )
  group by tablespace_name
) f,
( select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end ) tot_size
  from dba_data_files
  group by tablespace_name
  union all
  select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
  from dba_temp_files
  group by tablespace_name
) d
where d.tablespace_name like nvl(upper('&tablespace_prefix'),d.tablespace_name)||'%'
and f.tablespace_name(+) = d.tablespace_name
order by pct_used