Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Rebuilding a table with smaller columns

Re: Rebuilding a table with smaller columns

From: Michael Ray <Ihatespam_at_indy.net>
Date: Mon, 06 Feb 2006 16:55:47 GMT
Message-ID: <Xns9762795B74924topshotindynet@199.45.49.11>


"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US