------------------------------------------------------------------------
Gesendet mit der Telekom Mail App
<https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>
--- Original-Nachricht ---
Von: ahmed.fikri@xxxxxxxxxxx
Betreff: AW: AW: Re: Re: Column Length modification
Datum: 22. März 2021, 12:43
An: ahmed.fikri@xxxxxxxxxxx
sorry truncating the original table is required
------------------------------------------------------------------------
Gesendet mit der Telekom Mail App
<https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>
--- Original-Nachricht ---
Von: ahmed.fikri@xxxxxxxxxxx
Betreff: AW: Re: Re: Column Length modification
Datum: 22. März 2021, 12:41
An: loknath.73@xxxxxxxxx
Cc: oracle list
hi,
the data should be moved once (in your approach two times). To achieve that
your backup table should be partitioned. So after changing the col
definition in the original table change the partitions with the partitions
of the back-up table.
the data is moved only one time. And you don't need to trancute the
original table (this is safe)
------------------------------------------------------------------------
Gesendet mit der Telekom Mail App
<https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>
--- Original-Nachricht ---
Von: Lok P
Betreff: Re: Re: Column Length modification
Datum: 22. März 2021, 12:25
An: ahmed.fikri@xxxxxxxxxxx
Cc: oracle list
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@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> > 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@xxxxxxxxx
<mailto:loknath.73@xxxxxxxxx> > 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@xxxxxxxxx
<mailto:jlewisoracle@xxxxxxxxx> > 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@xxxxxxxxx
<mailto:loknath.73@xxxxxxxxx> > 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
