Oracle numbers in K/M/G/T/P/E

Franck Pachot
Jan 24 · 4 min read

Bytes

SQL> select name,dbms_xplan.FORMAT_SIZE(value) FORMAT_SIZE,dbms_xplan.FORMAT_SIZE2(value) FORMAT_SIZE2 from v$sysstat where name like 'bytes%SQL*Net%' and value>0 order by value desc;NAME                                     FORMAT_SIZE   FORMAT_SIZE2 
bytes received via SQL*Net from client 23T 23t
bytes sent via SQL*Net to client 12T 12t
bytes received via SQL*Net from dblink 165G 166g
bytes sent via SQL*Net to dblink 201M 202m

Numbers

SQL> select name,dbms_xplan.FORMAT_NUMBER(value) FORMAT_NUMBER,dbms_xplan.FORMAT_NUMBER2(value) FORMAT_NUMBER2 from v$sysstat where name like '%SQL*Net%roundtrips%' and value>0 order by value desc;NAME                                FORMAT_NUMBER   FORMAT_NUMBER2
SQL*Net roundtrips to/from client 9567M 10G
SQL*Net roundtrips to/from dblink 786K 787K
SQL> select event,dbms_xplan.FORMAT_NUMBER(total_waits) FORMAT_NUMBER,dbms_xplan.FORMAT_NUMBER2(total_waits) FORMAT_NUMBER2 from v$system_event where event like '%SQL*Net%' order by total_waits desc;EVENT                           FORMAT_NUMBER   FORMAT_NUMBER2   
SQL*Net message from client 9552M 10G
SQL*Net message to client 9514M 10G
SQL*Net more data to client 1383M 1G
SQL*Net more data from client 530M 530M
SQL*Net more data from dblink 5377K 5M
SQL*Net break/reset to client 863K 864K
SQL*Net message from dblink 800K 800K
SQL*Net message to dblink 786K 787K
SQL*Net break/reset to dblink 18 18

Seconds

SQL> select event,dbms_xplan.FORMAT_TIME_S(time_waited/100) FORMAT_TIME_S,time_waited/100 from v$system_event where event like '%SQL*Net%' order by total_waits desc;EVENT                           FORMAT_TIME_S   TIME_WAITED/100   
SQL*Net message from client 999:59:59 376461581.58
SQL*Net message to client 01:58:45 7124.77
SQL*Net more data to client 247:05:21 889521.16
SQL*Net more data from client 376:29:01 1355340.99
SQL*Net more data from dblink 04:09:47 14987.19
SQL*Net break/reset to client 00:46:26 2785.71
SQL*Net message from dblink 01:47:27 6447.04
SQL*Net message to dblink 00:00:01 0.7
SQL*Net break/reset to dblink 00:00:00 0
SQL> select to_number(column_value),dbms_xplan.format_time_s(to_number(column_value)) from xmltable('3599995 to 3600005');TO_NUMBER(COLUMN_VALUE) DBMS_XPLAN.FORMAT_TIME_S(TO_NUMBER(COLUMN_VA
----------------------- --------------------------------------------
3599995 999:59:55
3599996 999:59:56
3599997 999:59:57
3599998 999:59:58
3599999 999:59:59
3600000 999:59:59
3600001 999:59:59
3600002 999:59:59
3600003 999:59:59
3600004 999:59:59
3600005 999:59:59
SQL> select event,'+'||trunc(time_waited/100/60/60/24)||' '||dbms_xplan.FORMAT_TIME_S(mod(time_waited/100,3600*24)) "+days hh24:mi:ss",dbms_xplan.FORMAT_TIME_S(time_waited/100) FORMAT_TIME_S from v$system_event where event='PING' order by time_waited desc;EVENT +days hh24:mi:ss  FORMAT_TIME_S
----- ----------------- -------------
PING +45 08:18:11 999:59:59

Rounding

select to_char(n,'9.99999EEEE') e,n
,dbms_xplan.format_number(n) "NUMBER"
,dbms_xplan.format_number2(n) "NUMBER2"
,dbms_xplan.format_size(n) "SIZE"
,dbms_xplan.format_size2(n) "SIZE2"
from (
select to_number(column_value)-2+1000*1000 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1024*1024 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1e9 n from xmltable('1 to 3')
union all
select to_number(column_value)-2+1e12 n from xmltable('1 to 3')
)
;

NUMBER NUMBER2 SIZE SIZE2
9.99999E+05 999999 999K 1000K 976K 977k
1.E+06 1000000 1000K 1M 976K 977k
1.E+06 1000001 1000K 1M 976K 977k

1.04858E+06 1048575 1048K 1M 1023K 1024k
1.04858E+06 1048576 1048K 1M 1024K 1m
1.04858E+06 1048577 1048K 1M 1024K 1m

1.E+09 999999999 999M 1000M 953M 954m
1.E+09 1000000000 1000M 1G 953M 954m
1.E+09 1000000001 1000M 1G 953M 954m
1.E+18 999999999999999999 999P 1000P 888P 888p
1.E+18 1000000000000000000 1000P ##### 888P 888p
1.E+18 1000000000000000001 1000P ##### 888P 888p

The size of your database

select 
'Number of Files: '||dbms_xplan.FORMAT_NUMBER(count(*)),
'Database Size: '||dbms_xplan.FORMAT_SIZE(sum(bytes))
from dba_data_files;

Franck Pachot

Written by

https://twitter.com/FranckPachot DBA at CERN, Oracle OCM 12c, Oracle ACE Director, Oak Table member. My 499 posts at dbi-services: http://blog.dbi.pachot.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade