1.表空间使用量
SELECT Upper(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_GB "表空间大小(G)",
D.TOT_GROOTTE_GB - F.TOTAL_BYTES "已使用空间(G)",
To_char(Round((D.TOT_GROOTTE_GB - F.TOTAL_BYTES) / D.TOT_GROOTTE_GB * 100, 2), '990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(G)",
F.MAX_BYTES "最大块(G)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / (1024 * 1024 * 1024), 2) TOTAL_BYTES,
Round(Max(BYTES) / (1024 * 1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / (1024 * 1024 * 1024), 2) TOT_GROOTTE_GB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1;
2.TEMP表空间使用量
SELECT D.TABLESPACE_NAME,
FILE_NAME "FILE_NAME",
SPACE "SUM_SPACE(M)",
USED_SPACE "USED_SPACE(M)",
ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) "USED_RATE(%)",
AUTOEXTENSIBLE
FROM (SELECT FILE_ID,
FILE_NAME,
TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME, FILE_ID, FILE_NAME) D,
(SELECT FILE_ID,
TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME, FILE_ID) E,
(SELECT FILE_ID, AUTOEXTENSIBLE FROM DBA_TEMP_FILES) F
WHERE D.TABLESPACE_NAME = E.TABLESPACE_NAME(+)
AND D.FILE_ID = E.FILE_ID(+)
AND D.FILE_ID = F.FILE_ID(+)
ORDER BY TABLESPACE_NAME, FILE_NAME;