[20230726]使用dbms_xplan.format_size格式化数字输出.txt

来源：这里教程网时间：2026-03-03 18:56:53 作者：

[20230726]使用dbms_xplan.format_size格式化数字输出.txt --//dbms_xplan包多数情况下使用来显示执行计划.看了Franck Pachot的博客,作者通过dbms_xplan.format_size函数可以格式化数字输 --//出.通过几个简单的例子说明: --//另外参考链接:http://dbaparadise.com/2019/10/how-can-dbms_xplan-format_size-be-userful-for-you/ 1.环境: SCOTT@test01p> @ ver1 PORT_STRING VERSION BANNER CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 SCOTT@test01p> @desc_proc sys dbms_xplan format_% INPUT OWNER PACKAGE_NAME OBJECT_NAME sample : @desc_proc sys dbms_stats gather_%_stats OWNER PACKAGE_NAME OBJECT_NAME SEQUENCE ARGUMENT_NAME DATA_TYPE IN_OUT DEFAULTED ----- ------------ -------------- -------- ------------- --------- --------- ---------- SYS DBMS_XPLAN FORMAT_SIZE 1 VARCHAR2 OUT N 2 NUM NUMBER IN N FORMAT_NUMBER 1 VARCHAR2 OUT N 2 NUM NUMBER IN N FORMAT_SIZE2 1 VARCHAR2 OUT N 2 NUM NUMBER IN N FORMAT_NUMBER2 1 VARCHAR2 OUT N 2 NUM NUMBER IN N FORMAT_TIME_S 1 VARCHAR2 OUT N 2 NUM NUMBER IN N --//实际上是一系列函数,注意返回类型是字符类型,注意设定显示宽度. 2.例子: SCOTT@test01p> column nn format a16 SCOTT@test01p> column mm format a16 SCOTT@test01p> select dbms_xplan.format_size(1024) nn,dbms_xplan.format_size2(1024) mm from dual; NN MM -------------------- ---------------- 1024 1k SCOTT@test01p> select dbms_xplan.format_size(1024*1024) nn,dbms_xplan.format_size2(1024*1024) mm from dual; NN MM -------------------- ---------------- 1024K 1m SCOTT@test01p> select dbms_xplan.format_size(1024*1024*1024) nn,dbms_xplan.format_size2(1024*1024*1024) mm from dual; NN MM -------------------- ---------------- 1024M 1g SCOTT@test01p> select dbms_xplan.format_size(1024*1024*1024*1024) nn,dbms_xplan.format_size2(1024*1024*1024*1024) mm from dual; NN MM -------------------- ---------------- 1024G 1t --//dbms_xplan.format_size2 显示的字母是小写,而dbms_xplan.format_size显示的字母是大写,不知道oracle为什么这样设定. --//你可以使用它格式化显示对应数字.例子: column SEG_Size format a30 SELECT * FROM ( SELECT segment_name,dbms_xplan.format_size(bytes) as SEG_Size FROM dba_segments WHERE owner = 'XXX' AND segment_type = 'TABLE' ORDER BY bytes) WHERE rownum <= 10; SELECT FILE_NAME , DBMS_XPLAN.FORMAT_SIZE(BYTES) NN FROM DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE '%SYS%' ORDER BY BYTES; FILE_NAME NN ----------------------------------------------- ----- D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSAUX01.DBF 235M D:\APP\ORACLE\ORADATA\TEST\TEST01P\SYSTEM01.DBF 270M 3.继续: --//DBMS_XPLAN.FORMAT_NUMBER可以猜测除以1000.例子: SCOTT@test01p> select dbms_xplan.format_number(1024) nn,dbms_xplan.format_number2(1024) mm from dual; NN MM -------------------- ---------------- 1024 1K SCOTT@test01p> select dbms_xplan.format_number(1024*1000) nn,dbms_xplan.format_number2(1024*1000) mm from dual; NN MM -------------------- ---------------- 1024K 1M SCOTT@test01p> select dbms_xplan.format_number(2000*1000*1000) nn,dbms_xplan.format_number2(2000*1000*1000) mm from dual; NN MM -------------------- ---------------- 2000M 2G SCOTT@test01p> select dbms_xplan.format_number(1024*1024*1024*20) nn,dbms_xplan.format_number2(1024*1024*1024*20) mm from dual; NN MM -------------------- ---------------- 21G 21G --//dbms_xplan.format_number2显示的是大写,似乎做了四舍五入处理. 4.DBMS_XPLAN.FORMAT_TIME_S用于格式化时间输出,将秒转换为HH24:MI:SS格式,例子: SCOTT@test01p> select dbms_xplan.format_time_s(901) nn from dual; NN -------------------- 00:15:01 SCOTT@test01p> select dbms_xplan.format_time_s(901.45) nn from dual; NN -------------------- 00:15:01 SCOTT@test01p> select dbms_xplan.format_time_s(86410) nn from dual; NN -------------------- 24:00:10 SCOTT@test01p> select dbms_xplan.format_time_s(86410*9) nn from dual; NN -------------------- 216:01:30 4.附上 $ cat desc_proc.sql column owner format a10 column package_name format a30 column object_name format a30 column data_type format a30 column ARGUMENT_NAME format a30 column defaulted format a10 set verify off break on owner on package_name on object_name skip 1 PROMPT INPUT OWNER PACKAGE_NAME OBJECT_NAME PROMPT sample : @desc_proc sys dbms_stats gather_%_stats Prompt SELECT owner ,package_name ,object_name ,sequence ,argument_name ,data_type ,in_out ,defaulted FROM dba_arguments WHERE owner = NVL (UPPER ('&1'), 'SYS') AND package_name = NVL (UPPER ('&2'), 'DBMS_STATS') AND object_name LIKE NVL (UPPER ('&3'), object_name) AND data_level = 0 ORDER BY overload, subprogram_id,sequence; --ORDER BY owner,package_name,object_name,sequence;