Re: Column Length modification

If you're see an error then show us exactly what it is. I assume it's
ORA-01440: column to be modified must be empty to decrease precision or scale

You're trying to change a column from (15,5) to (22,0) which means you're going to lose 5 decimal places - do any of the rows have data that isn't purely integer, if not are you happy for the values to change as you go from 5d.p. to integer?

If you need 22 digits precision, and no decimal places you could modify your column to (27,5) and then add a check constraint that says (check colX = trunc(colX)) as a way of ensuring that you don't have any non-integer values. (You could update the table,set colX to trunc(colX) where colX != trunc(colX)before adding the constraint, or you could add the constraint enabled but not validated, then do the update then set the constraint validated. ** or ceiling() or round(), depending on what you think best suits your requirements

Regards
Jonathan Lewis

On Sat, 20 Mar 2021 at 19:10, Lok P <loknath.73_at_gmail.com> wrote:

> We are using version 11.2.0.4 of Oracle exadata. Our requirement is to
> modify column length of a table from Number(15,5) to Number(22) and we are
> seeing errors and its saying to make the column empty before making this
> modification. So to achieve this we are thinking of doing this in multiple
> steps like
>
> 1) Add new column(COL_new) with number(22,0) to the same table
>
> 2)Then update the new column with all the values of original column(say
> COL1)
>
> 3)Then drop the original column(COL1) which is having length number(15,5)
> 4)Then rename the new column(COL_NEW) to original i.e. COL1.
>
> We are in the process of doing multiple such modifications to some big
> partition and non partitioned table. And in this process the Update seems
> to be a tedious one as it will scan the full table and may lead to row
> chaining and also drop the existing column and renaming new columns will
> need the application to stop pointing to this object or else they may fail.
> Also stats seems to be gathered fully again on the table after this along
> with if any index pointing to these columns needs to be recreated. So
> multiple issues highlighted with this process by the team. Want to
> understand from experts if there exists any better way of achieving this
> with minimal interruption and in quick time?
>
>
> Thanks
>
> Lok
>

--
http://www.freelists.org/webpage/oracle-l