# Thread: how many bytes does NUMBER(n) column occupy

1. Senior Member
Join Date
May 2001
Posts
285
Hi,

For a column defined as NUMBER(n), how can I find out how many bytes it occupy?

Also, if I have a column defined as UniqueIdentifier which stores 16-byte hexadecimal values in SQL Server 2000, how can I find out how big the NUMBER column should be defined? i.e. how to decide the 'n'?

Last question, what's the value range for INTEGER data type in Oracle?

2. Pando & Company
Join Date
Jun 2000
Location
Posts
7,447

http://otn.oracle.com/docs/products/...typ.htm#427846

3. Senior Member
Join Date
May 2001
Posts
285

## I looked at the doc, but

that didn't answer my question. All it specifys is the NUMBER column will be a variable-length numeric data, and the maximum length of that datatype is 21 bytes. However, it didn't tell me how to calculate the number of bytes for different 'n' defined in NUMBER(n).

Any other idea?

4. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439

5. I followed the thread jmodic mentioned, and it is one of the amazing threads I find it here. Wonderful explanation of the subject matter.
Can anyone know now what is that 8th bytes for? And what is that Type 13?

Join Date
Jun 2001
Location
Helsinki. Finland
Posts
3,938

Can anyone know now what is that 8th bytes for? And what is that Type 13?
SQL> select vsize(sysdate) from dual;

VSIZE(SYSDATE)
--------------
8

SQL> select vsize(HIRE_DATE) from EMP where rownum=1;

VSIZE(HIRE_DATE)
--------------------
7

Why Oracle needs 8 bytes (1 more than usual) for sysdate?
Here is what I know now. DATE values are always stored in 7 bytes, excluding the length byte, within a datafile.

Let's first dump the dates:

SQL> SELECT dump(sysdate) FROM dual;

DUMP(SYSDATE)
------------------------------------------
Typ=13 Len=8: 209,7,10,19,9,44,19,0

SQL> SELECT dump(HIRE_DATE) FROM EMP WHERE ROWNUM = 1;

DUMP(HIRE_DATE)
--------------------------------------------------
Typ=12 Len=7: 120,101,10,17,1,1,1

Notice the type for sysdate. It is 13, not 12. 13 is the external DATE datatype. External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.

7. Julian,

That is quiet interesting, thanks.

Cheers

8. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
[QUOTE]Originally posted by julian
...[SNIP]...
Notice the type for sysdate. It is 13, not 12. 13 is the external DATE datatype. External datatype 13 is an internal c-structure whose length varies depending on how the c-compiler represents the structure. Note that the "Len=" value is 8 and not 7. Type 13 is not a part of the published 3GL interfaces for Oracle and is used for date calculations mainly within PL/SQL operations.
That was allready (more or less) covered in the original thread. However, the question remains: for what purpose does Oracle need the eight byte in type 13? No matter how you play with dates, the DUMP() will allways display the eight byte as 0. So what is its purpose? Any clue?

Join Date
Jun 2001
Location
Helsinki. Finland
Posts
3,938
That was allready (more or less) covered in the original thread. However, the question remains: for what purpose does Oracle need the eight byte in type 13? No matter how you play with dates, the DUMP() will allways display the eight byte as 0. So what is its purpose? Any clue?
Here are the storage format for type 13 data:

Byte 1 - Base 256 year modifier
2 - Base 256 year
3 - Month
4 - Day
5 - Hours
6 - Minutes
7 - Seconds
8 - Unused

Thus, the answer to your question "So what is its purpose" is:

There is no purpose. For now, Oracle does not use it.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•