Skip to Main Content
  • Questions

  • Question - increase size of number column

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Priyanka .

Asked: October 02, 2015 - 1:23 pm UTC

Last updated: April 02, 2024 - 5:46 am UTC

Version: 10

Viewed 10K+ times! This question is

You Asked

We just hit 2.1 billion row count on a table with primary key INT. This is the worse thing to happen :( Any one know if we can do alter without requiring space on the DB for the entire table?

and Connor said...

Should be an easy fix, even if there is existing data


SQL> create table T ( x number(4) );

Table created.

SQL> insert into T
  2  select rownum from dual
  3  connect by level <= 1000;

1000 rows created.

SQL>
SQL> insert into T values (10000);
insert into T values (10000)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL>
SQL> alter table T modify x number(10);

Table altered.

SQL>
SQL> insert into T values (10000);

1 row created.

SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

alter table modify number column with data exists

Renga, March 21, 2024 - 11:57 am UTC

suppose column with decimal say (sno number(8,4) and has data. If i want to increase the size (sno varchar2(8,5). Is that possible.
Connor McDonald
March 25, 2024 - 5:14 am UTC

You can't go from number to varchar2 without reloading the data.

Enlarging number column

mathguy, March 25, 2024 - 6:02 pm UTC

@Renga - I assume you want to enlarge to NUMBER(8,5), not VARCHAR2, right? VARCHAR2 doesn't have two specifiers, only one for length.

If so, the answer to your question is "no, that is not possible" - but perhaps because you didn't ask the question correctly. NUMBER(8,4) means number with eight digits of precision, OF WHICH four are after the decimal point. This means the integer part may be up to four digits. If you (try to) change to NUMBER(8,5), that would still mean eight digits of TOTAL precision, OF WHICH five are after the decimal point. So the number 3282.39 which could fit in NUMBER(8,4) will not fit in NUMBER(8,5) - because NUMBER(8,5) only has room for three digits to the right of the decimal point.

This is a common mistake (at least among beginners). If you want to increase the decimal part to five decimal places, you must increase BOTH the precision AND the scale of the NUMBER column. The proper way to ask the question is whether you can increase from NUMBER(8,4) to NUMBER(9,5). And the answer to that question is "yes, easily, and there are no complications".
Connor McDonald
March 27, 2024 - 7:12 am UTC

nice input

Mixing up left and right

mathguy, March 27, 2024 - 1:25 pm UTC

Oops - in my earlier reply, I mixed left and right. The end of the second paragraph should be:

NUMBER(8,5) only has room for three digits to the left of the decimal point.
Connor McDonald
April 02, 2024 - 5:46 am UTC

right

:-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.