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@xxxxxxxxx> 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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:
oracle-l-bounce@xxxxxxxxxxxxx] *On Behalf Of *Sayan Malakshinov
*Sent:* Monday, March 22, 2021 7:44 AM
*To:* Lok P
*Cc:* ahmed.fikri@xxxxxxxxxxx; 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@xxxxxxxxx> 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@xxxxxxxxxxx <
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> 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>
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> 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
