FROM (SELECT T.TABLESPACE_NAME,
SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
SUM(T.BLOCKS) TOTALBLOCKS
FROM DBA_DATA_FILES T
GROUP BY T.TABLESPACE_NAME) DBF,
(SELECT TT.TABLESPACE_NAME,
SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
SUM(TT.BLOCKS) FREEBLOCKS
FROM DBA_FREE_SPACE TT
GROUP BY TT.TABLESPACE_NAME) DFS
WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME)
select b.file_name 文件名,
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 文件大小,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.bytes;
2 个回复
我是最优雅的杀手,不杀人专杀狗 - 日光之下并无新事 2016-05-11 回答
赞同来自:
DBF.TOTALSPACE "总量(M)",
DBF.TOTALBLOCKS AS 总块数,
DFS.FREESPACE "剩余总量(M)",
DFS.FREEBLOCKS "剩余块数",
(DFS.FREESPACE / DBF.TOTALSPACE) * 100 "空闲比例"
FROM (SELECT T.TABLESPACE_NAME,
SUM(T.BYTES) / 1024 / 1024 TOTALSPACE,
SUM(T.BLOCKS) TOTALBLOCKS
FROM DBA_DATA_FILES T
GROUP BY T.TABLESPACE_NAME) DBF,
(SELECT TT.TABLESPACE_NAME,
SUM(TT.BYTES) / 1024 / 1024 FREESPACE,
SUM(TT.BLOCKS) FREEBLOCKS
FROM DBA_FREE_SPACE TT
GROUP BY TT.TABLESPACE_NAME) DFS
WHERE TRIM(DBF.TABLESPACE_NAME) = TRIM(DFS.TABLESPACE_NAME)
li052468 2016-05-11 回答
赞同来自: 老头子 、梁勇
b.tablespace_name 表空间,
b.bytes / 1024 / 1024 文件大小,
(b.bytes - sum(nvl(a.bytes, 0))) / 1024 / 1024 已使用
from dba_free_space a, dba_data_files b
where a.file_id = b.file_id
group by b.tablespace_name, b.file_name, b.bytes
order by b.bytes;