Update: To see internal representation given a human readable number, use dump(xxx, 16). To see human readable number given internal representation, use dbms_stats.convert_raw_value. SQL> select dump(123433, 16) from dual; DUMP(123433,16) ----------------------- Typ=2 Len=4: c3,d,23,22 SQL> var n number SQL> exec dbms_stats.convert_raw_value('C30D2322', :n) PL/SQL procedure successfully completed. SQL> print N ---------- 123433 SQL> select dump(-123433, 16) from dual; DUMP(-123433,16) --------------------------- Typ=2 Len=5: 3c,59,43,44,66 SQL> exec dbms_stats.convert_raw_value('3C59434466', :n) PL/SQL procedure successfully completed. SQL> print N ---------- -123433 SQL> select dump(-123.433, 16) from dual; DUMP(-123.433,16) ------------------------------ Typ=2 Len=6: 3d,64,4e,3a,47,66 SQL> exec dbms_stats.convert_raw_value('3D644E3A4766', :n) PL/SQL procedure successfully completed. SQL> print N ---------- -123.433 Note that dump and convert_raw_value are not limited to number data type. How Oracle Stores Numbers If your table contains a value 123 for a NUMBER data type, the datafile does not store it as 123; instead it's stored as c2 02 18. The following describes how to interpret the cryptic storage. (Reference:http://www.jlcomp.demon.co.uk/num_size.html) First, some preparatory work. In order to see the content of a datafile, you have to dump the file. To do this, select * from dba_segments where segment_name = 'YOURTABLE'. Look at columns HEADER_FILE and HEADER_BLOCK. Suppose your table is very small (for our purpose, it's much easier to use such a small table) so we know the first block beyond the header block is the data block that contains our newly-inserted row. Run alter system dump datafile block where is HEADER_FILE from the first SQL statement we ran, is HEADER_BLOCK + 1. Open the trace file thus created in udump directory and look at the rows beginning with col1, col2, etc. The rule to interpret positive integers in a datafile dump is as follows. The first byte is about exponent. You subtract c1 from it (why c1? I don't know). The difference times 2 will be the exponent if this byte is less than 0a; else if this byte is greater than or equal to 0a, times 2 plus 1. The rest of the bytes are characters representing numbers in BCD (binary coded decimal) format, but they also need to be subtracted by 1. You then convert them to decimal and concatinate. Lastly, apply the exponent. Example: c3 0c 0c 0c 2 12 12 12 <-- c3 minus c1, 0c converted to decimal 12 5 11 11 11 <-- 2 doubled to 4 and plus 1 (because the second byte is 0c >= 0a), three 12's minus 1 1.11111 x 10**5 = 111111 Suppose you had inserted 11111 instead of 111111. The dump file would show: c3 02 0c 0c 2 2 12 12 4 1 11 11 1.1111 x 10**4 = 11111 Negative integers are stored quite differently. I'll write a separate article about it later, again. Note that if you only need to see how Oracle stores or would store a particular piece of data, you may not have to insert it into a table and dump the relevant datafile. Using the DUMP function as in SELECT DUMP(11111,16) FROM DUAL should do the trick most of the time. Sometimes it may not work. E.g, SELECT DUMP(SYSDATE) FROM DUAL gives different numbers from those coming from a datafile dump; even the number of numbers dumped is different. If you only need to know how many bytes are used to store a particular datatype, you should use the VSIZE function. Dumping a datafile for that purpose is, again, an overkill. You may wonder why knowing how Oracle stores numbers is useful. Since Oracle does not directly store numbers (not storing 3 as 3, -30.5 as -30.5 etc), you can't say for sure that comparing numbers is faster than comparing varchars in a WHERE clause, even though generally speaking, a computer does number comparison faster than string comparison. I.e., whether you define EMPNO as datatype NUMBER or VARCHAR2 is really not because of performance, but probably for reason of preventing ' 123' from being entered. In fact, someone once claimed at an Oracle mailing list that comparing varchars (as in where empno = '1234') is faster than comparing numbers (as in where empno = 1234) if the datatype is defined as varchar and number, respectively. P.S. Everybody says the number is stored as exponent and mantissa. But the word "mantissa" is the decimal part of a logarithm. E.g., log(123.4) is 2.0913. The mantissa of 2.0913 is 0913. It makes no sense talking about the mantissa of the original number 123.4 (as people would say 1234 is its mantissa), unless you think of 123.4 as the result of a log function on another number. Yong Huang yong321@yahoo.com