SELECT A.TABLESPACE_NAME AS "TABLESPACE"
, A.BYTES / 1024 / 1024 AS "TOTAL(MB)"
, (A.BYTES - NVL(B.FREEBYTES,0)) / 1024 / 1024 AS "Used(MB)"
, NVL(B.FREEBYTES,0) / 1024 / 1024 AS "Free(MB)"
, ROUND(NVL(B.FREEBYTES,0)*100/A.BYTES,2) AS "Free(%)"
FROM (
SELECT TABLESPACE_NAME
, SUM(BYTES) BYTES
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) A
, (
SELECT TABLESPACE_NAME
, SUM(NVL(BYTES,0)) FREEBYTES
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5 ASC;
좀 오래 걸리긴 함. ...
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0), '99999999.999') "Used (M)",
TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "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",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.bytes, 0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)",
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';
