Column Length modification

[Lok P]

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

[a***@t-online.de]

hi,

create a table with the right structure, copy the data into the new, drop
the old one, rename the new.

Best regards
Ahmed



------------------------------------------------------------------------
Gesendet mit der Telekom Mail App
<https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>


--- Original-Nachricht ---
Von: Lok P
Betreff: Column Length modification
Datum: 20. MÀrz 2021, 20:09
An: Oracle L


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

["Shane Borden" (Redacted sender "sborden76" for DMARC)]

DBMS_REDEFINITION is your friend. Perfect use case for this.

Shane Borden
***@yahoo.com
Sent from my iPhone


hi,
create a table with the right structure, copy the data into the new, drop the old one, rename the new.
Best regards
Ahmed
Gesendet mit der Telekom Mail App
--- Original-Nachricht ---
Von: Lok P
Betreff: Column Length modification
Datum: 20. MÀrz 2021, 20:09
An: Oracle L
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

[Jonathan Lewis]

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

Post by Lok P
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

[Lok P]

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

Post by Jonathan Lewis
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

Post by Lok P
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