AW: Re: Column Length modification

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

• 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
<mailto: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   <mailto: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     <mailto: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

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