oracle学习之-表空间查询.txt
免费下载
select b.b1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2 / 1024 / 1024 表空间大小 M,
(b.b2 - nvl(a.a2,0)) / 1024 / 1024 已使用 M,
round((b.b2 - nvl(a.a2,0)) / b.b2 * 100, 2) 利用率
from (select tablespace_name a1, sum(nvl(bytes, 0)) a2
from dba_free_space
group by tablespace_name) a,
(select tablespace_name b1, sum(bytes) b2
from dba_data_files
group by tablespace_name) b,
(select tablespace_name c1, contents c2, extent_management c3
from dba_tablespaces) c
where a.a1(+) = b.b1
and c.c1(+) = b.b1
order by 6 desc;
SELECT B.B1 AS "表空间名称",
C.C2 AS "类型",
C.C3 AS "区管理",
ROUND(B.B2 / 1024 / 1024 / 1024, 2) AS "表空间总计(G)",
ROUND((B.B2 - NVL(A.A2, 0)) / 1024 / 1024 / 1024, 2) AS "已使用空间(G)",
ROUND(NVL(A.A2, 0) / 1024 / 1024 / 1024, 2) AS "剩余空间(G)",
ROUND((B.B2 - NVL(A.A2, 0)) / B.B2, 4) * 100 AS "利用率(%)"
FROM (SELECT TABLESPACE_NAME A1, SUM(NVL(BYTES, 0)) A2
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) A,
(SELECT TABLESPACE_NAME B1, SUM(BYTES) B2
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) B,
(SELECT TABLESPACE_NAME C1, CONTENTS C2, EXTENT_MANAGEMENT C3
FROM DBA_TABLESPACES) C
WHERE A.A1(+) = B.B1
AND C.C1(+) = B.B1
ORDER BY "利用率(%)" DESC;
of 1
免费下载
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文档的来源（墨天轮），文档链接，文档作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
下载排行榜
评论