Re: Re: Column Length modification

If forced to make it visible as NUMBER(22), then I'd suggest leveraging a view. Something like:

alter table my_table

   modify tweaked_column number(27,5) check (tweaked_column = trunc(tweaked_column));
alter table my_table

   rename to my_table_old;
create view my_table as

   select col1, col2, ..., cast(tweaked_column as number(22)) tweaked_column, ... from my_table_old;

Nice and quick*, with minimal undo/redo generation. Albeit with the caveat that the table is briefly unavailable when the switcheroo takes place.

• If the table is large enough that constraint validation time is a significant concern, you may want to create it using ENABLE NOVALIDATE and perform validation as a separate step. This allows validation to occur without the need for a full table lock, which prevents it from blocking transactions.

On Tue, Mar 23, 2021 at 12:54 PM Lok P <loknath.73_at_gmail.com> wrote:

> Thank you all.
>
> To sum up , I think the best way is if we can live with the number(27,5)
> i.e. with the same old precision but with just a large length of integer,
> which will be a dictionary modification for Oracle and can be done in
> online fashion within seconds without impacting dependent application.
>
> But in case we are forced to make the column visible or described as
> NUMBER(22,0) to make it consistent across all the upstream and down
> streams, then in the current version , the best option would be to copy
> data and change the column length in a blank/truncated table and copy data
> back. And I hope this is also achievable online using dbms_redefinition, so
> should be okay. Correct me if wrong.
>
> I am still trying to digest how the option of making a wrapper view with
> NVL(colname,old_col) will help? Will this method also help us keep the new
> column as NUMBER(22,0)?
>
> Regards
> Lok
>
> On Mon, Mar 22, 2021 at 11:08 PM Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> sayan wrote:
>>
>>
>>
>> NVL(colname,old_col)
>>
>>
>>
>> which is brilliant, especially if you have time based partitioning and
>> eventual “unhook partition” purging at some age (like the common 7 years or
>> 25 years for EPA projects). At some point then, no values would exist in
>> old_col and you could drop the wrapper, without ever copying squat, since
>> all your new values go into the “new” colname without changing a line of
>> code.
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:
>> oracle-l-bounce_at_freelists.org] *On Behalf Of *Sayan Malakshinov
>> *Sent:* Monday, March 22, 2021 7:44 AM
>> *To:* Lok P
>> *Cc:* ahmed.fikri_at_t-online.de; oracle list
>> *Subject:* 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
>>
>

-- 
"In the beginning the Universe was created. This has made a lot of people
very angry and been widely regarded as a bad move." -Douglas Adams

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