Oracle查看表空间终极SQL

查看表空间使用率:
select ta.tablespace_name "表空间名称",
       decode(tb.contents,'UNDO','UNDO表空间','TEMPORARY','临时表空间','数据表空间') "表空间类型",
       round(ta.tablespace_size*8/1024,2) "表空间总容量(MB)",
       round(ta.used_space*8/1024,2) "表空间已使用容量(MB)",
       round((ta.tablespace_size-used_space)*8/1024,2) "表空间剩余容量(MB)",
       round(ta.used_percent,2) "表空间使用率"
from dba_tablespace_usage_metrics ta,
     dba_tablespaces tb
where ta.tablespace_name = tb.tablespace_name
order by round(ta.used_percent,2) 

-- 查看表空间创建时间
select a.file_name,a.tablespace_name,to_char(b.creation_time,'yyyy-mm-dd') creation_time 
from dba_data_files a,v$datafile b 
where a.file_id=b.file# order by tablespace_name;

--表空间查询终极SQL
select * from (select d.status "Status",
              d.tablespace_name "Name",
              d.contents "Type",
              d.extent_management "Extent Management",
              round(NVL(a.bytes / 1024 / 1024, 0), 2) "Size (M)",
              round(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, 2) "Used (M)",
         round(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), 2) "Used %"
         from sys.dba_tablespaces d,
              (select tablespace_name, sum(bytes) bytes
                 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.extent_management like 'LOCAL' AND
               d.contents like 'TEMPORARY')
       union all
       select d.status "Status",
              d.tablespace_name "Name",
              d.contents "Type",d.extent_management "Extent Management",
              round(NVL(a.bytes / 1024 / 1024, 0), 2) "Size (M)",
              round(NVL(t.bytes, 0) / 1024 / 1024, 2) "Used (M)",
              round(NVL(t.bytes / a.bytes * 100, 0), 2) "Used %"
         from sys.dba_tablespaces d,
              (select tablespace_name, sum(bytes) bytes
                 from db a_temp_files
                group by tablespace_name) a,
              (select tablespace_name, sum(bytes_cached) bytes
                 from v$temp_extent_pool
                group by tablespace_name) t
        where d.tablespace_name = a.tablespace_name(+)
          and d.tablespace_name = t.tablespace_name(+)
          and d.extent_management like 'LOCAL'
          and d.contents like 'TEMPORARY')
order by "Used %" desc;