## Question and Answer

## 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

We're not taking comments currently, so please try again later if you want to add a comment.

# Comments

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.

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

@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(

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".
nice input

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

NUMBER(8,5) only has room for three digits to the

**left**of the decimal point.
right

:-)

:-)