Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Rebuilding a table with smaller columns
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in
news:0fadnYHhL6oMW3veRVn-vA_at_comcast.com:
>
> the precision constrains, but technically is not a constraint. and the
> max value of a 12,7 will take more storage than a 7,3
>
> also, you can't reduce precision of a column that contains data:
>
> SQL> create table t2(n number(12,7));
>
> Table created.
>
> SQL> insert into t2 values(0);
>
> 1 row created.
>
> SQL> alter table t2 modify n number(7,3);
> alter table t2 modify n number(7,3)
> *
> ERROR at line 1:
> ORA-01440: column to be modified must be empty to decrease precision
> or scale
That is true for the alter table command, but not doing an import on a precreated table with a smaller column. At least it works using an 8i export with a 10g import. It rounds the precision off just like you would expect. Unfortunately, it more than doubles the import time (since this is by far the largest table), which is why I ask the question. Is there a quicker way to reduce the scale/precision of a column? Received on Mon Feb 06 2006 - 10:55:47 CST