speed up alter table modify varchar2(4000) to varchar2(3900)
Hello,
we need to modify width of 12 varchar2 columns from 4000 to 3900.
There is large table and
alter table ORIGINAL_DOC
modify (
BUF1 VARCHAR2(3900),
BUF10 VARCHAR2(3900),
BUF11 VARCHAR2(3900),
BUF12 VARCHAR2(3900),
BUF2 VARCHAR2(3900),
BUF3 VARCHAR2(3900),
BUF4 VARCHAR2(3900),
BUF5 VARCHAR2(3900),
BUF6 VARCHAR2(3900),
BUF7 VARCHAR2(3900),
BUF8 VARCHAR2(3900),
BUF9 VARCHAR2(3900)
)
;
takes from hour to 7 hours depends on table size.
There are 12 recursive calls: select /*+ first_rows */ 1 from "X"."ORIGINAL_DOC" where LENGTHB("BUF9") > 3900.
It is not possible to use functional indexes lengthb(buf1)..lengthb(buf12) for ORA-30556.
Any unrecommended parameters to avoid ORA-30556?
Any another ideas how to speed up such modification?
I can't use dbms_redefinition because of another constraints.
we need to modify width of 12 varchar2 columns from 4000 to 3900.
There is large table and
alter table ORIGINAL_DOC
modify (
BUF1 VARCHAR2(3900),
BUF10 VARCHAR2(3900),
BUF11 VARCHAR2(3900),
BUF12 VARCHAR2(3900),
BUF2 VARCHAR2(3900),
BUF3 VARCHAR2(3900),
BUF4 VARCHAR2(3900),
BUF5 VARCHAR2(3900),
BUF6 VARCHAR2(3900),
BUF7 VARCHAR2(3900),
BUF8 VARCHAR2(3900),
BUF9 VARCHAR2(3900)
)
;
takes from hour to 7 hours depends on table size.
There are 12 recursive calls: select /*+ first_rows */ 1 from "X"."ORIGINAL_DOC" where LENGTHB("BUF9") > 3900.
It is not possible to use functional indexes lengthb(buf1)..lengthb(buf12) for ORA-30556.
Any unrecommended parameters to avoid ORA-30556?
Any another ideas how to speed up such modification?
I can't use dbms_redefinition because of another constraints.
0