DBMS_REDEFINITION is your friend. Perfect use case for this.
Shane Borden
sborden76@xxxxxxxxx
Sent from my iPhone
On Mar 20, 2021, at 5:17 PM, ahmed.fikri@xxxxxxxxxxx wrote:
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
