data length v/s data precision

Taking in account the definition from Oracle:

DATA_LENGTH is the length of the column (in bytes). DATA_PRECISION is the decimal precision for NUMBER datatype, the binary precision for FLOAT datatype, null for all other datatypes. DATA_SCALE is the digits to right of decimal point in a number

Let's put an example:

SQL> create table check_size ( c1 number, c2 number(6) , c3 number(6,3) );

Table created.

SQL> set lines 200
SQL> r
  1  SELECT COLUMN_NAME,
  2            DATA_TYPE,
  3            DATA_LENGTH,
  4               DATA_PRECISION,
  5               DATA_SCALE
  6              FROM ALL_TAB_COLS
  7      WHERE OWNER='TEST'
  8*     AND TABLE_NAME = 'CHECK_SIZE'

COLUMN_NAME                    DATA_TYPE                      DATA_LENGTH DATA_PRECISION DATA_SCALE
------------------------------ ------------------------------ ----------- -------------- ----------
C1                             NUMBER                                  22
C2                             NUMBER                                  22              6          0
C3                             NUMBER                                  22              6          3

As you can see, data_length is always 22. That is because numbers are stored as packets, so the dictionary will show you always 22, but I can insert a number greater than 22 digits.

SQL>  insert into check_size values ( 99999999999999999999999999999999999999999999 , 999999, 999.99 );

1 row created.

SQL> select dump(c1) as content from check_size ;

CONTENT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Typ=2 Len=2: 215,2

However, in your case you say data precision 5 and data scale 1, so let's truncate the table and modify the column:

SQL> truncate table check_size ;

Table truncated.

SQL> alter table check_size modify c3 number ( 5,1 );

Table altered.

SQL>  insert into check_size values ( 99999999999999999999999999999999999999999999 , 999999, 99 );

1 row created.

SQL>  insert into check_size values ( 99999999999999999999999999999999999999999999 , 999999, 9999.9 );

1 row created.

SQL>  insert into check_size values ( 99999999999999999999999999999999999999999999 , 999999, 9999.99 );
 insert into check_size values ( 99999999999999999999999999999999999999999999 , 999999, 9999.99 )
                                                                                        *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column

Summarising, with data scale 5 and data precision 1, you can store a number in any type up to this maximum precision + scale. So I can store 9999.9 and 99 ( scale and precision are advisory ) but I cannot 9999.99 , because my scale is 1 and here the number has 2 decimals.

Hope it clarifies Regards

Since your data length of your column is (22, 5), it can accommodate (6, 2) as well. If you still want to restrict the data length to (6, 2) for any technical/ business requirement, you can use below alter statement to modify your column.

alter table table_name modify column_name number(6, 2);

Basically, the DATA_LENGTH has no significance for numeric columns. It is intended for CHAR/VARCHAR types. If the type is numeric, just look at DATA_PRECISION and DATA_SCALE.

Also for numeric types, DATA_LENGTH is the maximal storage size in bytes. However, in contrast to the CHAR/VARCHAR types, the type parameters (in this case, precision and scale) are not taken into account: The value 22 is set for all numeric types, even if they are so restricted that they can never actually reach the 22 bytes.

I believe that the reason for the value 22 is as follows:

• Oracle stores numbers with two digits in one byte. Since NUMBER/NUMERIC officially supports up to 38 digits in Oracle, one might think that this needs up to 19 bytes. However, Oracle internally uses a (mantissa,exponent) representation, where the exponent is relative to base 100, and not base 10 (because the two digits in a byte can be seen as one base-100 digit). Now if the decimal point is at an odd position, the first and the last byte effectively contain only one digit. Therefore, 20 bytes are needed. In fact, one can store numbers with 39 digits or even 40 digits (depending on the decimal point position) in a column of type NUMBER. This also supports the 20 bytes assumption.
• One byte is needed for the exponent (with base 100).
• One additional byte is needed if the number is negative.

The storage length can be checked with the function VSIZE:

SELECT VSIZE(-12345678901234567890123456789012345678)
FROM DUAL

The result is 21 (as expected). Without the minus sign, it is 20.

Unfortunately, I was not able to get the value 22 by adding more digits. If one adds one more digit, one gets the value 21 for a positive number:

SELECT VSIZE(123456789012345678901234567890123456789)
FROM DUAL

This looks good. But if one now makes this number negative, the result is still 21 (whereas for all smaller numbers that I tested, making the number negative adds one byte). [I used Oracle 18c XE.]