Re: Column Length modification

I wouldn't consider rewriting terabytes of data just for the sake of being able to describe a column as number(22,0) instead of number(27,5) with a check constraint and a little bit of documentation. Whatever you do you introduce a significant risk of an undesirable performance impact or space unless you're very careful and a bit lucky with your analysis of every single table you want to modify.

If you really want to do this then it would be best to wait until you've upgraded to at least 12.2 since that gives you a wider range of strategies - iincluding options for online moves, eliminating rows you no longer need, etc.. If you're going to do it in 11.2 then a strategy which involves copying the data is a lot safer than trying to add columns, update, then drop old columns (and it's also likely to be much less resource intensive).

Depending on the way the data arrives, is modified, and archived the details of the way you copy/correct the data may vary from table to table. If you're going to be rewrting partitioned tables (in particular) you might want to consider doing something useful at the same time - e.g. for "old" partitions of locally indexed tables there may be some mileage in creating the copy one partition at a time, sorting it and compressing it as you do so.

Regards
Jonathan Lewis

On Sun, 21 Mar 2021 at 02:39, Lok P <loknath.73_at_gmail.com> wrote:

> Thank you Jonathan.
>
> Yes it's exactly the same error ORA-01440: which we are encountering. And
> the column is not having any data with non zero precision. But as we are
> standardizing the data elements across our applications, we want to now not
> allow any junks in future and thus trying to fix the precision thing as
> part of this length modification. This will make things consistent across
> all our applications and easy for understanding.
>
> Now if my understanding is correct, the way you are suggesting i..e
> altering column length as (22,7) (which will not make any ORA-01440 error
> happen )+ having the check constraint added to the table will technically
> help us achieving the same thing (without any additional performance
> overhead) as simply altering the length to number(22,0). But is it
> something that will create confusion and thus we should keep it clean i.e.
> column length (22,0) only without any additional constraint? And to achieve
> that , is the best approach is the one suggested by Ahmed i.e. create the
> object fully with the new structure(i.e. with number(22,0)) and then drop
> the old one and rename the new one?
>
> Regards
> Lok
>
>

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