监控表空间

set lines 300 pages 1000
col tablespace_name for a30
select *
from
(
SELECT A.TABLESPACE_NAME,
ROUND(A.BYTES_ALLOC / 1024 / 1024 /1024) AS "SIZE_ALLOC(GB)",
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0)) / 1024 / 1024 /1024 ) AS "SIZE_USED(GB)",
ROUND((A.BYTES_ALLOC - NVL(B.BYTES_FREE, 0))*100/A.MAXBYTES) AS "USED_OF_MAX",
ROUND((A.MAXBYTES - A.BYTES_ALLOC + NVL(B.BYTES_FREE, 0))/1048576/1024) AS "FREE_OF_MAX(GB)",
ROUND(A.MAXBYTES / 1048576/1024) AS "SIZE_OF_MAX(GB)"
FROM (SELECT F.TABLESPACE_NAME,
SUM(F.BYTES) BYTES_ALLOC,
SUM(DECODE(F.AUTOEXTENSIBLE, 'YES', F.MAXBYTES, 'NO', F.BYTES)) MAXBYTES
FROM DBA_DATA_FILES F
GROUP BY TABLESPACE_NAME) A,
(SELECT F.TABLESPACE_NAME, SUM(F.BYTES) BYTES_FREE
FROM DBA_FREE_SPACE F
GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
)size_info
order by 4 DESC;