以MB为单位
col TABLESPACE_NAME for a20 SELECT D.TABLESPACE_NAME,SPACE "Total(M)", SPACE-NVL(FREE_SPACE,0) "USED(M)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED(%)",FREE_SPACE "FREE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfile SELECT D.TABLESPACE_NAME,SPACE "Total(M)", USED_SPACE "USED(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED(%)", NVL(FREE_SPACE,0) "FREE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
以GB为单位
col TABLESPACE_NAME for a20 SELECT D.TABLESPACE_NAME,SPACE "Total(G)", SPACE-NVL(FREE_SPACE,0) "USED(G)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED(%)",FREE_SPACE "FREE(G)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfile SELECT D.TABLESPACE_NAME,SPACE "Total(M)", USED_SPACE "USED(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,3) "USED(%)", NVL(FREE_SPACE,0) "FREE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024*1024),3) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024*1024),3) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
以GB为单位,。中文显示，如果有乱码使用上面语句。
col 表空间名称 for a20 SELECT D.TABLESPACE_NAME "表空间名称",SPACE "总大小(G)", SPACE-NVL(FREE_SPACE,0) "已使用(G)", ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "已使用率(%)",FREE_SPACE "剩余(G)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) UNION ALL --if have tempfile SELECT D.TABLESPACE_NAME,SPACE "Total(M)", USED_SPACE "USED(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,3) "USED(%)", NVL(FREE_SPACE,0) "FREE(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024*1024),3) SPACE,SUM(BLOCKS) BLOCKS FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) D, (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024*1024),3) USED_SPACE, ROUND(SUM(BYTES_FREE)/(1024*1024*1024),3) FREE_SPACE FROM V$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) F WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
原创文章请注明转载自技术小站本文地址：http://www.i5i6.net/post/57.html，标题：Oracle查看表空间使用情况
