常用sql语句-临时段使用情况

临时段使用情况

SELECT d.tablespace_name "TABLESPACE_NAME",
      TO_CHAR(NVL(a.bytes / 1024 / 1024, 0), '99999999') "SUM(MB)",
      TO_CHAR(NVL(t.bytes, 0) / 1024 / 1024, '999999999') "USED(MB)",
      TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "USED(%)",
      (a.bytes - t.bytes) / 1024 / 1024 "FREE(MB)",
      d.status "Status"
  FROM sys.dba_tablespaces d,
      (select tablespace_name, sum(bytes) bytes
          from dba_temp_files
        group by tablespace_name) a,
      (select tablespace_name, sum(bytes_cached) bytes
          from gv$temp_extent_pool
        group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.contents like 'TEMPORARY'
order by 4 desc;