前言：
如今大家对“oracle小数前面的0”大致比较关切，你们都需要了解一些“oracle小数前面的0”的相关内容。那么小编也在网络上汇集了一些对于“oracle小数前面的0””的相关资讯，希望小伙伴们能喜欢，我们快快来学习一下吧！查询表空间，获取到使用百分比时 小数点前的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查询结果如下：
标签： #oracle小数前面的0
