Oracle Numbers Length Mystery - DBA Gurus - HELP !!!

Hi everybody,

I wonder if anybody can explain following problem:

When you create a table with number column, in view dba_tab_columns you will find, that data_length for this column has value 22. I wonder why, because according to manuals maximal data length is 21 ( it's true - see following test )
I inserted into this table 2 numbers with 40 significant digits ( which is more than 38 allowed) and the second number is with negative sign, just to be sure, that Oracle will not use this 22-nd byte for the sign ( according to manual it will use one itional byte only for numbers with less than 38 significant digits )

SQL> create table g ( n number );

Table created.

SQL> set pages 2000
SQL> select * from user_tab_columns where table_name='G';

TABLE_NAME                     COLUMN_NAME

   DATA_TYPE

• --------- DATA_LENGTH DATA_PRECISION DATA_SCALE N COLUMN_ID DEFAULT_LENGTH
  ----------- -------------- ---------- - ----------
  

  ---

  DATA_DEFAULT

  ---

  ---

  NUM_DISTINCT LOW_VALUE

  ---

  ---

  ---

  HIGH_VALUE DENSITY

  ---

  • ---------- G N NUMBER 22 Y 1

SQL> insert into g
  2 values ( 1234567890123456789012345678901234567890);

1 row created.

SQL> select vsize(n) from g;

  VSIZE(N)

        21

SQL> insert into g values
  2 (-1234567890123456789012345678901234567890);

1 row created.

SQL> select vsize(n) from g;

  VSIZE(N)

        21
        21

SQL> select * from g;

         N

SQL> As you can see, both numbers really take just 21 bytes.I wonder if anybody can explain why in view dba_tab_columns is value 22 ( I expected 21 ).

(We are running Oracle 7.1.4.1.0 on HP-UX 9.05 )

Thanks in advance

Brano.

Email: valny_at_slovnaft.sk
Fax: +427244931 Received on Mon Nov 27 1995 - 00:00:00 CET