oracle学习之-表空间查询

  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;