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
