表空间使用率和增长趋势分析
set lin 200
set pages 2000
set ti on
set timi n
col tablespace_name for a30
SELECT
df.tablespace_name, COUNT (*) datafile_count,
ROUND (SUM (df.BYTES) / 1048576/1024,2)
size_gb,
ROUND (SUM (free.BYTES) / 1048576/1024,2)
free_gb,
ROUND (SUM (df.BYTES) / 1048576/1024 - SUM
(free.BYTES) / 1048576/1024,2) used_gb,
ROUND (MAX (free.maxbytes) / 1048576/1024,2)
maxfree,
100 - ROUND (100.0 * SUM (free.BYTES) / SUM
(df.BYTES), 2) pct_used,
ROUND (100.0 * SUM (free.BYTES) / SUM
(df.BYTES), 2) pct_free
FROM dba_data_files df,
(SELECT
tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes
FROM dba_free_space GROUP BY tablespace_name,
file_id) free
WHERE df.tablespace_name =
free.tablespace_name(+) AND df.file_id = free.file_id(+)
GROUP BY df.tablespace_name
ORDER BY 8
