本文簡單介紹兩種檢視錶空間使用情況的方法.

方法一、
SELECT c.tablespace_name "表空間", ROUND(a.bytes/1048576,2) "表空間大小",ROUND((a.bytes-b.bytes)/1048576,2) "已使用空間",ROUND(b.bytes/1048576,2) "剩餘空間",
ROUND(b.bytes/a.bytes * 100,2)||'%' "剩餘百分比" FROM
(SELECT tablespace_name,SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
ORDER BY ROUND(b.bytes/1048576,2);

suk@ORA9I> /

表空間表空間大小已使用空間剩餘空間剩餘百分比
------------------------------ ---------- ---------- ---------- -----------------------------------------
TOOLS 10 .06 9.94 99.38%
INDX 25 .06 24.94 99.75%
USERS 25 .06 24.94 99.75%
SYSTEM 250 183.06 66.94 26.78%
UNDOTBS1 200 42.19 157.81 78.91%
SUK 500 .38 499.63 99.93%

方法二、
select a.tablespace_name,round(nvl(sum(a.bytes)/1024/1024,0),2) "ALL_SIZE(M)",round(nvl(sum(b.bytes)/1024/1024,0),2) "USED_SIZE(M)",
round(sum(c.bytes)/1024/1024,2) "FREE_SIZE(M)",
round(100*nvl(sum(c.bytes),0)/sum(a.bytes),2)||'%' "FREE_PCT(%)"
from sm$ts_avail a,sm$ts_used b,sm$ts_free c
where a.tablespace_name=b.tablespace_name(+) and a.tablespace_name=c.tablespace_name(+)
group by a.tablespace_name
order by 3;

TABLESPACE_NAME ALL_SIZE(M) USED_SIZE(M) FREE_SIZE(M) FREE_PCT(%)
------------------------------ ----------- ------------ ------------ -----------------------------------------
INDX 25 0 24.94 99.75%
TOOLS 10 0 9.94 99.38%
USERS 25 0 24.94 99.75%
SUK 500 .31 499.63 99.93%
UNDOTBS1 200 42.05 157.81 78.91%
SYSTEM 250 183 66.94 26.78%

--需要用左連線，否則，出來的結果會沒有沒有使用過的表空間。

兩種方法都需要相應的許可權。
第二種方法不是非常準確，但無關大局。

兩種檢視錶空間使用情況的方法

相關文章