常用sql语句–表空间使用情况

--包含数据文件自动扩展

SELECT  
d.tablespace_name "Name", 
d.status "Status", 
d.contents "Type", 
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99G999G990D900') "Size_MB", 
TO_CHAR(NVL(b.bytes - NVL(f.bytes, 0),0)/1024/1024, '99G999G990D900') "Used_MB", 
TO_CHAR(NVL((b.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990D00') "Useds" 
FROM sys.dba_tablespaces d, 
(SELECT tablespace_name, 
SUM( CASE WHEN autoextensible = 'YES' THEN maxbytes when autoextensible = 'NO' THEN bytes END) bytes
FROM  dba_data_files  GROUP BY tablespace_name) a, 
(select tablespace_name, sum(bytes) bytes 
from dba_data_files group by tablespace_name) b,
(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 = b.tablespace_name(+)
order by 6;

--不包含数据文件自动扩展

SELECT d.tablespace_name "Name",
       d.extent_management,
       d.status "Status",
       d.contents "Type",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99G999G990D900') "Size (MB)",
       to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024,'99G999G990D900') "Used (MB)",
       to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0),'990D00') "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 d.contents <> 'TEMPORARY'
UNION ALL
SELECT d.tablespace_name "Name",
       d.extent_management,
       d.status "Status",
       d.contents "Type",
       to_char(nvl(a.bytes / 1024 / 1024, 0), '99,999,990.900') "Size (MB)",
       nvl(t.bytes, 0) / 1024 / 1024 || '/' ||
       nvl(a.bytes / 1024 / 1024, 0) "Used (MB)",
       to_char(nvl(t.bytes / a.bytes * 100, 0), '990.00') "Used%"
  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 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 7 DESC;