WITH tmp AS
(
SELECT file_id, tablespace_name, file_name,
DECODE (autoextensible,
'YES', GREATEST (BYTES, maxbytes),
BYTES
) mysize,
DECODE (autoextensible,
'YES', CASE
WHEN (maxbytes > BYTES)
THEN (maxbytes - BYTES)
ELSE 0
END,
0
) growth
FROM dba_data_files)
SELECT tmp.tablespace_name,
ROUND (SUM (tmp.mysize) / (1024 * 1024)) total_size,
ROUND (SUM (growth) / (1024 * 1024)) growth,
ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) datafile_free_space,
ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
) total_free_space,
ROUND ( (SUM (NVL (freebytes, 0)) + SUM (growth))
/ SUM (tmp.mysize)
* 100
) percent_free
FROM tmp, (SELECT file_id, SUM (BYTES) freebytes
FROM dba_free_space
GROUP BY file_id) dfs
WHERE tmp.file_id = dfs.file_id(+)
GROUP BY tmp.tablespace_name
ORDER BY 6 DESC
(
SELECT file_id, tablespace_name, file_name,
DECODE (autoextensible,
'YES', GREATEST (BYTES, maxbytes),
BYTES
) mysize,
DECODE (autoextensible,
'YES', CASE
WHEN (maxbytes > BYTES)
THEN (maxbytes - BYTES)
ELSE 0
END,
0
) growth
FROM dba_data_files)
SELECT tmp.tablespace_name,
ROUND (SUM (tmp.mysize) / (1024 * 1024)) total_size,
ROUND (SUM (growth) / (1024 * 1024)) growth,
ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) datafile_free_space,
ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
) total_free_space,
ROUND ( (SUM (NVL (freebytes, 0)) + SUM (growth))
/ SUM (tmp.mysize)
* 100
) percent_free
FROM tmp, (SELECT file_id, SUM (BYTES) freebytes
FROM dba_free_space
GROUP BY file_id) dfs
WHERE tmp.file_id = dfs.file_id(+)
GROUP BY tmp.tablespace_name
ORDER BY 6 DESC
No comments:
Post a Comment