Re: Re: Column Length modification

Hi Lok,

I would prefer Jonathan's approach or renaming old column and creating new one with the same name, but with new precision and wrapper view with NVL(colname,old_col).
But if you really want to change it, I would change your approach: instead of p.1 "create backup table using CTAS", I would create partitioned table backup_table and use "alter table backup_table switch partition with old_table".
It will make an original table empty, so you don't need p.2.

On Mon, Mar 22, 2021 at 2:25 PM Lok P <loknath.73_at_gmail.com> wrote:

> Thank You So much. So just to avoid missing any grants synonyms and
> related consequences like invalidation of package/procedure etc. Is it
> good to just intact the main table but move data in and out , something as
> below..
> 1)Create a backup table as "tab1_backup" from the main table TAB1 using
> CTAS approach using parallelism without creating indexes constraints etc on
> the backup table.
> 2)Truncate the main table TAB1.
> 3)make the indexes UNUSABLE in the main table TAB1.
> 4)Alter the column in the main table TAB1 to modify the existing column
> from number(15,5) to number(22,0).
> 5)Insert data into the main table(tab1) from the backup table(tab1_backup)
> using direct path load + parallel threads. And in case of unusable indexes
> this step should be pretty fast.
> 6)Rebuild the indexes in the main table.
>
> Regards
> Lok
>
>
>
> On Mon, Mar 22, 2021 at 4:16 PM ahmed.fikri_at_t-online.de <
> ahmed.fikri_at_t-online.de> wrote:
>
>> oracle does the same when you add the new colum, update it and remove the
>> old one (so doing this, the table is recreated three times).
>>
>> just use direct load + parallel + constraints novalidate. This work
>> perfectly even for huge tables
>>
>>
>>
>> ------------------------------
>> Gesendet mit der Telekom Mail App
>> <https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>
>>
>>
>> --- Original-Nachricht ---
>> *Von: *Lok P
>> *Betreff: *Re: Column Length modification
>> *Datum: *22. März 2021, 11:38
>> *An: *Jonathan Lewis
>> *Cc: *Oracle L
>>
>>
>> Just that, i think the option of creating the new object fully with the
>> data and rename afterwards by dropping the original object may not be a
>> good option if the object which we are trying to alter is in TB's and
>> partitioned, thinking if any other possible way to achieve the same?
>>
>> On Sun, Mar 21, 2021 at 8:09 AM 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
>>>
>>> On Sun, Mar 21, 2021 at 3:30 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>>> wrote:
>>>
>>>>
>>>> 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
>>>>
>>>>
>>>>
>>>>
>>>> On Sat, 20 Mar 2021 at 19:10, Lok P <loknath.73_at_gmail.com> wrote:
>>>>
>>>>> 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
>>>>>
>>>>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

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