DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

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

  1. #1
    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. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    may be this chapter in Oracle documentation might help you,

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

  3. #3
    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. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    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?

  6. #6
    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. #7
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Julian,

    That is quiet interesting, thanks.

    Cheers
    OCP 8i, 9i DBA
    Brisbane Australia

  8. #8
    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?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    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
  •  


Click Here to Expand Forum to Full Width