oracle常用sql.rar

方法一、 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); 方法二、 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;