|
查询表空间，获取到使用百分比时 小数点前的0不显示问题： 以下列SQL为例： select a.TABLESPACE_NAME, TSPACE_USED_PCT, MSPACE_USED_PCT from (SELECT D.TABLESPACE_NAME TABLESPACE_NAME, SPACE TOTAL_SPACE, max_space, SPACE - NVL(FREE_SPACE, 0) SPACE_USED, FREE_SPACE, ROUND(((SPACE - NVL(FREE_SPACE, 0)) / SPACE) * 100, 2) || '%' TSPACE_USED_PCT, ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) || '%' MSPACE_USED_PCT FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS, round(sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024), 2) max_space 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 select a.tablespace_name, total_space, max_space, nvl(used_space, 0) used_space, (total_space - nvl(used_space, 0)) free_space, trunc((nvl(used_space, 0) / total_space) * 100, 1) || '%' tspace_used_pct, trunc((nvl(used_space, 0) / max_space) * 100, 1) || '%' mspace_used_pct from (select tablespace_name, sum(bytes) / 1024 / 1024 total_space, round(sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024), 2) max_space from dba_temp_files group by tablespace_name) a, (select su.tablespace, sum(su.blocks * dt.block_size) / 1024 / 1024 used_space from v$sort_usage su, dba_tablespaces dt where su.tablespace = dt.tablespace_name group by tablespace) b where a.tablespace_name = b.tablespace(+)) a, dba_tablespaces b where a.TABLESPACE_NAME = b.tablespace_name 使用Navicat工具进行查询，结果如下： 能够明显看到表UNDOTBS1的MSPACE.USED_PCT 列返回值在小数点前少了个0。 自行按照百度上的方法，使用to_char对第二个select中的MSPACE_USED_PCT进行处理，结果发现有2个小数点，不是很满意。 解决方法： 依旧使用to_char函数进行格式化解决，但只在第一个select 查询列的结果时使用，统一了格式。 参考文档： SQL如下： select a.TABLESPACE_NAME, to_char(TSPACE_USED_PCT,'9999990.99')||'%' AS TSPACE_USED_PCT, to_char(MSPACE_USED_PCT,'9999990.99')||'%' AS MSPACE_USED_PCT from (SELECT D.TABLESPACE_NAME TABLESPACE_NAME, SPACE TOTAL_SPACE, max_space, SPACE - NVL(FREE_SPACE, 0) SPACE_USED, FREE_SPACE, ROUND(((SPACE - NVL(FREE_SPACE, 0)) / SPACE) * 100, 2) TSPACE_USED_PCT, ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) MSPACE_USED_PCT FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS, round(sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024), 2) max_space 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 select a.tablespace_name, total_space, max_space, nvl(used_space, 0) used_space, (total_space - nvl(used_space, 0)) free_space, trunc((nvl(used_space, 0) / total_space) * 100, 1) tspace_used_pct, trunc((nvl(used_space, 0) / max_space) * 100, 1) mspace_used_pct from (select tablespace_name, sum(bytes) / 1024 / 1024 total_space, round(sum(decode(maxbytes, 0, bytes, maxbytes) / 1024 / 1024), 2) max_space from dba_temp_files group by tablespace_name) a, (select su.tablespace, sum(su.blocks * dt.block_size) / 1024 / 1024 used_space from v$sort_usage su, dba_tablespaces dt where su.tablespace = dt.tablespace_name group by tablespace) b where a.tablespace_name = b.tablespace(+)) a, dba_tablespaces b where a.TABLESPACE_NAME = b.tablespace_name; 使用Navicat查询结果如下：
| 
