oracle查看表空间的真实使用情况
--查看表空间的真实使用情况 set linesize 500 pagesize 500 col tablespace_name format a25 col TP_REAL_GB format a15 col TP_REAL_FREE_GB format a20 select all_tp.TP_NAME "TABLESPACE_NAME", to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099') "TP_REAL_GB", to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099') "TP_REAL_FREE_GB", (to_char(trunc(all_tp.TP_ALL_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099') - to_char(trunc(free_tp.TP_FREE_SIZE_KB / 1024 / 1024, 2), 'FM9999990.0099')) "TP_REAL_USED_GB", to_char(trunc(free_tp.TP_FREE_SIZE_KB * 100 / all_tp.TP_ALL_SIZE_KB, 2), 'FM9999990.0099') || '%' "TP_FREE_RATING" from ( --表空间总大小 select sum(TP_SIZE_KB) TP_ALL_SIZE_KB, TP_NAME from ( --自动扩展总大小(maxbytes/bytes取两者最大值) select decode(sign(ddf.maxbytes - ddf.bytes), 1, ddf.maxbytes, ddf.bytes) / 1024 TP_SIZE_KB, ddf.tablespace_name TP_NAME from DBA_DATA_FILES ddf where ddf.autoextensible = 'YES' union all --非自动总扩展大小 select BYTES / 1024 TP_SIZE_KB, ddf.tablespace_name TP_NAME from DBA_DATA_FILES ddf where ddf.autoextensible = 'NO') TP_ALL_SIZE group by TP_NAME) all_tp, ( --表空间空闲的总大小 select sum(TPF_SIZE_KB) TP_FREE_SIZE_KB, TP_NAME from ( --数据文件已经分配,空闲空间 select dfs.bytes / 1024 TPF_SIZE_KB, dfs.tablespace_name TP_NAME from DBA_FREE_SPACE dfs union all --数据文件自动扩展(若maxbytes大于bytes,取差值;若maxbytes小于等于bytes,取0) select decode(sign(ddf.maxbytes - ddf.bytes), 1, ddf.maxbytes - ddf.bytes, 0) / 1024 TPF_SIZE_KB, ddf.tablespace_name TP_NAME from DBA_DATA_FILES ddf where ddf.autoextensible = 'YES') TP_FREE_SIZE group by TP_NAME) free_tp where all_tp.TP_NAME = free_tp.TP_NAME(+);
TABLESPACE_NAME TP_REAL_GB TP_REAL_FREE_GB TP_REAL_USED_GB TP_FREE_RATING ------------------------- --------------- -------------------- --------------- -------------- SYSAUX 31.99 31.51 .48 98.48% UNDOTBS1 31.99 31.98 .01 99.94% USERS 31.99 31.99 0 99.98% SYSTEM 31.99 31.33 .66 97.93% EXAMPLE 31.99 31.92 .07 99.75% Elapsed: 00:00:00.18
作者:九命猫幺
博客出处:http://www.cnblogs.com/yongestcat/
欢迎转载,转载请标明出处。
如果你觉得本文还不错,对你的学习带来了些许帮助,请帮忙点击右下角的推荐
博客出处:http://www.cnblogs.com/yongestcat/
欢迎转载,转载请标明出处。
如果你觉得本文还不错,对你的学习带来了些许帮助,请帮忙点击右下角的推荐