Post date: Jul 26, 2013 8:25:6 AM
SELECT d.status,
d.tablespace_name,
d.contents TYPE,
d.extent_management extent_mgt,
d.segment_space_management segment_mgt,
NVL (a.bytes, 0) ts_size,
NVL (f.bytes, 0) free,
NVL (a.bytes - NVL (f.bytes, 0), 0) used,
TO_CHAR (
TRUNC (NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0)))
pct_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,
d.tablespace_name,
d.contents TYPE,
d.extent_management extent_mgt,
d.segment_space_management segment_mgt,
NVL (a.bytes, 0) ts_size,
NVL (a.bytes - NVL (t.bytes, 0), 0) free,
NVL (t.bytes, 0) used,
TO_CHAR (TRUNC (NVL (t.bytes / a.bytes * 100, 0))) pct_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 9 DESC;