Database Administration (MOSC)

MOSC Banner

speed up alter table modify varchar2(4000) to varchar2(3900)

edited Mar 26, 2009 4:53AM in Database Administration (MOSC) 4 commentsAnswered
  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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center