linux 查看表空间的挂载,两种查看表空间使用情况的方法

本文简单介绍两种查看表空间使用情况的方法.

方法一、

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%

--需要用左连接，否则，出来的结果会没有没有使用过的表空间。

两种方法都需要相应的权限。

第二种方法不是非常准确，但无关大局。