Re: Column Length modification

DBMS_REDEFINITION is your friend. Perfect use case for this.

Shane Borden
sborden76_at_yahoo.com
Sent from my iPhone

> On Mar 20, 2021, at 5:17 PM, ahmed.fikri_at_t-online.de 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

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