how many bytes does NUMBER(n) column occupy

[elaine3839]

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?

[pando]

may be this chapter in Oracle documentation might help you,

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

[elaine3839]

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?

[jmodic]

Check the following thread:

http://www.dbasupport.com/forums/sho...threadid=16402

[reydp]

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?

[julian]

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.

[grjohnson]

Julian,

That is quiet interesting, thanks.

Cheers

[jmodic]

[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?

[julian]

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.