Re: ORA-29874 DRG-50857: oracle error in dreii1fsh ORA-12899: value too large for column "DR$TOKEN" (actual: 69, maximum: 64)

From: Mikhail Velikikh <mvelikikh@xxxxxxxxx>
To: Sayan Malakshinov <xt.and.r@xxxxxxxxx>
Date: Wed, 4 Nov 2020 13:12:09 +0000

Hi Sayan,

I tried to emulate this error but there are too many variables when text
indexes come into play.
If you can share your index script, it can help to identify what the cause
might be.

select ctx_report.create_index_script('TMDDBA.tm_cc_tc_item_txt_cat') from
dual;

I also played a little bit with the following snippet
https://gist.github.com/mvelikikh/c9f3936270b9b4bab9554e30f0935638 ;,
utilizing ctx_output logging and sql_trace:

exec ctx_output.start_log(null)
exec ctx_output.add_event(ctx_output.event_index_print_rowid)

alter session set events 'sql_trace bind=true';
alter index tm_cc_tc_item_txt_cat rebuild;

exec ctx_output.end_log

I got some information that might come in handy to identify the root cause
- here is an excerpt from my trace file:

13:04:12 11/04/20 INDEXING ROWID AAASTMAAHAAAAIcAAA

13:04:12 11/04/20 Begin writing index to database.

PARSING IN CURSOR #139837124007000 len=86 dep=1 uid=106 oct=2 lid=106

tim=8980965396 hv=1205735491 ad='660bee90' sqlid='dtdcgvp3xw423'
insert into "TC"."DR$TM_CC_TC_ITEM_TXT_CAT$I"  values (:token, :ttype,
:trid, :tdata )
END OF STMT
BINDS #139837124007000:

Bind#0
  oacdty=01 mxl=2000(255) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=01 csi=01 siz=2056 off=0
  kxsbbbfp=7f2e5e0a6670  bln=2000  avl=30  flg=05
  value="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"
Bind#1
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=00 csi=00 siz=0 off=2000
  kxsbbbfp=7f2e5e0a6e40  bln=22  avl=01  flg=01
  value=0
Bind#2
  oacdty=01 mxl=32(18) mxlc=00 mal=00 scl=00 pre=00
  oacflg=00 fl2=0000 frm=01 csi=01 siz=0 off=2024
  kxsbbbfp=7f2e5e0a6e58  bln=32  avl=18  flg=01
  value="AAASTMAAHAAAAIcAAA"
Bind#3
  oacdty=23 mxl=128(100) mxlc=00 mal=00 scl=00 pre=00
  oacflg=12 fl2=0000 frm=00 csi=00 siz=128 off=0
  kxsbbbfp=7f2e5dd7b1e8  bln=128  avl=01  flg=09
  value=
EXEC
#139837124007000:c=17988,e=22433,p=0,cr=32,cu=54,mis=1,r=1,dep=1,og=1,plh=0,tim=8980987926

Best regards,
Mikhail Velikikh

On Wed, 4 Nov 2020 at 11:53, Sayan Malakshinov <xt.and.r@xxxxxxxxx> wrote:

Hi listers,

Does anybody know how to fix "ORA-12899: value too large for column
"DR$TOKEN" (actual: 69, maximum: 64)" after migrating from Oracle 12.2 to
Oracle 19?
Have you seen this exact error on CTX indexes before?

SQL> alter index tm_cc_tc_item_txt_cat rebuild;
alter index tm_cc_tc_item_txt_cat rebuild
*
ERROR at line 1:
ORA-29874: warning in the execution of ODCIINDEXALTER routine
ORA-29960: line 1,
DRG-50857: oracle error in dreii1fsh
ORA-12899: value too large for column
"TMDDBA"."DR$TM_CC_TC_ITEM_TXT_CAT$I"."DR$TOKEN" (actual: 69, maximum: 64)

--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE Associate
http://orasql.org

References:
- ORA-29874 DRG-50857: oracle error in dreii1fsh ORA-12899: value too large for column "DR$TOKEN" (actual: 69, maximum: 64)
  - From: Sayan Malakshinov

Re: ORA-29874 DRG-50857: oracle error in dreii1fsh ORA-12899: value too large for column "DR$TOKEN" (actual: 69, maximum: 64)

Other related posts: