Received: (qmail 23088 invoked from network); 29 Aug 2011 15:33:36 -0500
Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180)
  by static-ip-85-25-126-90.inaddr.ip-pool.com with SMTP; 29 Aug 2011 15:32:41 -0500
Received: from localhost (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1F219E3C3B1;
 Mon, 29 Aug 2011 16:32:12 -0400 (EDT)
DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org;
 s=turing; t=1314649932; bh=QgX8rs2QifOMOn2vax65F3KLGYqlzBpdMGzbIxhF
 Fnw=; h=References:Message-ID:Date:From:Subject:To:In-Reply-To:
	 MIME-Version:Content-Type:Sender:Reply-To:List-help:
	 List-unsubscribe:List-Id:List-subscribe:List-owner:List-post:
	 List-archive; b=mYssj6BBwbhfl+nN66rbsv4glXHWVUN11HlhPJaT2bl3l/SCjQ
 zrODzTgeV41ON94teDpqmJukKvOccDibVsvGQfqGIW6NZuS5UyGss+kkQQSEwBggfEn
 9xfm2oY8IrJzze8MZcaMNxOHBqDT4gue1aXmFls/C9MHZGLu0IXB30=
X-Virus-Scanned: Debian amavisd-new at localhost.localdomain
Received: from turing.freelists.org ([127.0.0.1])
 by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)
 with ESMTP id k+K84MzKVq5Q; Mon, 29 Aug 2011 16:32:11 -0400 (EDT)
Received: from turing.freelists.org (localhost [127.0.0.1])
 by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id D3FFBE3C1F6;
 Mon, 29 Aug 2011 16:31:27 -0400 (EDT)
DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=freelists.org;
 s=turing; t=1314649929; bh=QgX8rs2QifOMOn2vax65F3KLGYqlzBpdMGzbIxhF
 Fnw=; h=References:Message-ID:Date:From:Subject:To:In-Reply-To:
	 MIME-Version:Content-Type:Sender:Reply-To:List-help:
	 List-unsubscribe:List-Id:List-subscribe:List-owner:List-post:
	 List-archive; b=fYuZ8LPBNcbaKfD70WkA2UQa9x2PfsbeS1b+8P2WCs3d5KpUr1
 XEHo2BuY26/mFMsZlLZGgjLA2zgOydIaNRXuAnvp18COaObwnIGFWDRRyEsgknKIoIm
 GdXrdA27VkcurAKmCSOF6BIq8io7qFBp29ah9FHHhGAPpID/3xxbzA=
Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 29 Aug 2011 16:30:46 -0400 (EDT)
Received: from localhost (localhost [127.0.0.1])	by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 671C8E3C216	for <oracle-l@freelists.org>; Mon, 29 Aug 2011 16:30:45 -0400 (EDT)
Authentication-Results: turing.freelists.org; dkim=pass (1024-bit key) header.i=@yahoo.com
Received: from turing.freelists.org ([127.0.0.1])	by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024)	with ESMTP id Mmb2VF2spl6B for <oracle-l@freelists.org>;	Mon, 29 Aug 2011 16:30:45 -0400 (EDT)
Received: from nm24.bullet.mail.ac4.yahoo.com (nm24.bullet.mail.ac4.yahoo.com [98.139.52.221])	by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 221BCE3C205	for <oracle-l@freelists.org>; Mon, 29 Aug 2011 16:30:43 -0400 (EDT)
Received: from [98.139.52.192] by nm24.bullet.mail.ac4.yahoo.com with NNFMP; 29 Aug 2011 20:30:43 -0000
Received: from [98.139.52.183] by tm5.bullet.mail.ac4.yahoo.com with NNFMP; 29 Aug 2011 20:30:43 -0000
Received: from [127.0.0.1] by omp1066.mail.ac4.yahoo.com with NNFMP; 29 Aug 2011 20:30:43 -0000
X-Yahoo-Newman-Property: ymail-3
X-Yahoo-Newman-Id: 110909.42132.bm@omp1066.mail.ac4.yahoo.com
Received: (qmail 18689 invoked by uid 60001); 29 Aug 2011 20:30:43 -0000
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1314649842; bh=m6UoklQGJ+Z/4fUEPe0s+RAmses9UCzHbjIvQMrRttE=; h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type; b=lext/XbcFvIe2pC6RknFUhPl10C9CxGnfA+x0Lu+wwU4bXDcfCATgROknqQ+wGxhwLL1Ur3iEaaafTgrBYxOsSVXREzLyfQDnqYkgxEzOlGzoU7cULxyw5AvLYHil+mngOxBKE3yPKd80IJsiNWulI88os6AN+psDi6hugmwXmM=
DomainKey-Signature:a=rsa-sha1; q=dns; c=nofws;  s=s1024; d=yahoo.com;  h=X-YMail-OSG:Received:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type;  b=4YtIPEpmCfzJLtoSzyd7I9GRion7kOEd+mUhoHKuo1TD3fnSbyia4A7DdRxAWxhsCJJ9zyrLc/aApb1euoaVoWgqf+WfpAflYe38gg+R2Ray0OZ46eYDyq0G2m88SKB0boH6SRdcBIIbtmb7xC6jdGErpuzeCSjjv3AZ2DiimAA=;
X-YMail-OSG: wQ.ElfgVM1kwefIz5MGaDQHChtjBRA3a5znr74MMVao9uLj i1t_a7NK2MzwzfmaGmv.RZXaZjmr.VIEgeBUNdoGBa31Gqeg9KFFTGvK_pYC 66hbDLWd9NPT.NbF13be.bedrwej2r_qTDyqV9827x0l0Nx9RpVHWHZltRUa yVTvVl_MduLmTNoVqTrywLJeOvcDHnEZnkovC9BgBumLeD3OhkgyNHFvxjsD 01zFtpW8kj2Es9eF6BHh0gnGFCKSZt21lGdClVolWRzdjDw.GOO94nU3Azn3 fycuvDsBzrA48XUK1Qb6M0jyjO.xeJE_rAugWUrlTIbM47Ik2hVzd81KsTdp xUAU1gEj8ZxvuuCR3.LJC_cSega5YgTdDOIa2qHCEgxr5yHucUZjfTAjO1T9 7F6leGD6eJhLmDhriJF1SdMgb2TCLN5pbCcd0RKuDMntwpKTfApXjTG777D2 .5LL9n19sNDCNBpkHQ_rZw193vGpeF6UWPCnh5KOyBodoVyxKdWmI0WDja.Q 17T4KWS0E5INc8awqZbjVEAZeAQfFGIMHxgLibGi7lOGCLg--
Received: from [208.240.243.170] by web65402.mail.ac4.yahoo.com via HTTP; Mon, 29 Aug 2011 13:30:42 PDT
References: <8702332A6FE19B4FB526047905C1A1EEA914C4C380@DC2EXCVS01.ES.AD.ADP.COM> <30F0E1CB5AC745DA95E2E7DA0E0F719B@Primary> <8702332A6FE19B4FB526047905C1A1EEA914CE79B2@DC2EXCVS01.ES.AD.ADP.COM> <8702332A6FE19B4FB526047905C1A1EEA914CE7A5F@DC2EXCVS01.ES.AD.ADP.COM>
Message-ID: <1314649842.15352.YahooMailNeo@web65402.mail.ac4.yahoo.com>
Date: Mon, 29 Aug 2011 13:30:42 -0700 (PDT)
From: David Fitzjarrell <oratune@yahoo.com>
Subject: Re: ORA-01450: maximum key length (3215) exceeded -- Can't create index online with an nvarchar2 column
To: "Mark_Kudryk@ADP.com" <Mark_Kudryk@ADP.com>,  "oracle-l@freelists.org" <oracle-l@freelists.org>
In-Reply-To: <8702332A6FE19B4FB526047905C1A1EEA914CE7A5F@DC2EXCVS01.ES.AD.ADP.COM>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="0-1770168762-1314649842=:15352"
X-archive-position: 38421
X-ecartis-version: Ecartis v1.0.0
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
X-original-sender: oratune@yahoo.com
Precedence: normal
Reply-To: oratune@yahoo.com
List-help: <mailto:ecartis@freelists.org?Subject=help>
List-unsubscribe: <oracle-l-request@freelists.org?Subject=unsubscribe>
List-software: Ecartis version 1.0.0
List-Id: oracle-l <oracle-l.freelists.org>
X-List-ID: oracle-l <oracle-l.freelists.org>
List-subscribe: <oracle-l-request@freelists.org?Subject=subscribe>
List-owner: <mailto:steve.adams@ixora.com.au>
List-post: <mailto:oracle-l@freelists.org>
List-archive: <http://www.freelists.org/archives/oracle-l>
X-list: oracle-l
--0-1770168762-1314649842=:15352
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Before you do check how NLS_LENGTH_SEMANTICS is set; it may be set for CHAR=
 rather than BYTE.=A0 If it's set correctly (BYTE) and this still occurs su=
bmit an SR.=0A=0A=0ADavid Fitzjarrell=0A=0A=0AFrom: "Kudryk, Mark" <Mark_Ku=
dryk@ADP.com>=0ATo: "oracle-l@freelists.org" <oracle-l@freelists.org>=0ASen=
t: Monday, August 29, 2011 1:08 PM=0ASubject: RE: ORA-01450: maximum key le=
ngth (3215) exceeded -- Can't create index online with an nvarchar2 column=
=0A=0AI've looked at the trace.=A0 Here's the portion where the index is be=
ing created...=0A=0A*******************************************************=
*************************=0A=0Acreate index MK_INDEX_NVARCHAR_N351 ON mk_te=
st_length_n351 (data_field_id, =0A=A0 UPPER(additional_data_n351), invoice_=
id) tablespace indx online=0A=0A=0Acall=A0 =A0 count=A0 =A0 =A0 cpu=A0 =A0 =
elapsed=A0 =A0 =A0 disk=A0 =A0 =A0 query=A0 =A0 current=A0 =A0 =A0 =A0 rows=
=0A------- ------=A0 -------- ---------- ---------- ---------- ----------=
=A0 ----------=0AParse=A0 =A0 =A0 =A0 1=A0 =A0 =A0 0.01=A0 =A0 =A0 0.02=A0 =
=A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 1=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =
=A0 0=0AExecute=A0 =A0 =A0 1=A0 =A0 =A0 0.02=A0 =A0 =A0 0.01=A0 =A0 =A0 =A0=
 =A0 0=A0 =A0 =A0 =A0 =A0 1=A0 =A0 =A0 =A0 27=A0 =A0 =A0 =A0 =A0 0=0AFetch=
=A0 =A0 =A0 =A0 0=A0 =A0 =A0 0.00=A0 =A0 =A0 0.00=A0 =A0 =A0 =A0 =A0 0=A0 =
=A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=0A------- -----=
-=A0 -------- ---------- ---------- ---------- ----------=A0 ----------=0At=
otal=A0 =A0 =A0 =A0 2=A0 =A0 =A0 0.03=A0 =A0 =A0 0.03=A0 =A0 =A0 =A0 =A0 0=
=A0 =A0 =A0 =A0 =A0 2=A0 =A0 =A0 =A0 27=A0 =A0 =A0 =A0 =A0 0=0A=0AMisses in=
 library cache during parse: 1=0AOptimizer mode: FIRST_ROWS=0AParsing user =
id: 121=A0 =0A*************************************************************=
*******************=0A=0AAnd here is the portion where the IOT is being cre=
ated.=0A=0A****************************************************************=
****************=0A=0Acreate table "DOCPADMIN"."SYS_JOURNAL_425558" (C0 NUM=
BER, C1 NVARCHAR2(1053), =0A=A0 C2 NUMBER,=A0 opcode char(1), partno number=
,=A0 rid rowid, primary key( C0, C1, =0A=A0 C2 , rid )) organization index =
TABLESPACE "INDX"=0A=0A=0Acall=A0 =A0 count=A0 =A0 =A0 cpu=A0 =A0 elapsed=
=A0 =A0 =A0 disk=A0 =A0 =A0 query=A0 =A0 current=A0 =A0 =A0 =A0 rows=0A----=
--- ------=A0 -------- ---------- ---------- ---------- ----------=A0 -----=
-----=0AParse=A0 =A0 =A0 =A0 1=A0 =A0 =A0 0.02=A0 =A0 =A0 0.00=A0 =A0 =A0 =
=A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=0A=
Execute=A0 =A0 =A0 1=A0 =A0 =A0 0.01=A0 =A0 =A0 0.01=A0 =A0 =A0 =A0 =A0 0=
=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 3=A0 =A0 =A0 =A0 =A0 0=0AFetch=A0 =
=A0 =A0 =A0 0=A0 =A0 =A0 0.00=A0 =A0 =A0 0.00=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =
=A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 0=0A------- ------=A0=
 -------- ---------- ---------- ---------- ----------=A0 ----------=0Atotal=
=A0 =A0 =A0 =A0 2=A0 =A0 =A0 0.03=A0 =A0 =A0 0.01=A0 =A0 =A0 =A0 =A0 0=A0 =
=A0 =A0 =A0 =A0 0=A0 =A0 =A0 =A0 =A0 3=A0 =A0 =A0 =A0 =A0 0=0A=0AMisses in =
library cache during parse: 1=0AOptimizer mode: FIRST_ROWS=0AParsing user i=
d: 121=A0 =A0 (recursive depth: 1)=0A**************************************=
******************************************=0A=0AInitially when I read this,=
 I assumed the NVARCHAR2(1053) was defined in bytes (351 * 3).=A0 But it's =
not... it's in characters, the data length of this field in the IOT will ac=
tually be 3159.=A0 Plugging this value into the formula, I get an overall l=
ength of 3217, which exceeds the limit.=0A=0AI'll raise an SR.=0A=0AMark=0A=
=0A-----Original Message-----=0AFrom: oracle-l-bounce@freelists.org [mailto=
:oracle-l-bounce@freelists.org] On Behalf Of Kudryk, Mark (ES)=0ASent: Mond=
ay, August 29, 2011 1:54 PM=0ATo: oracle-l@freelists.org=0ASubject: RE: ORA=
-01450: maximum key length (3215) exceeded -- Can't create index online wit=
h an nvarchar2 column=0A=0AThanks. Mea Culpa on the length.=0A=0A-----Origi=
nal Message-----=0AFrom: oracle-l-bounce@freelists.org [mailto:oracle-l-bou=
nce@freelists.org] On Behalf Of Jonathan Lewis=0ASent: Monday, August 29, 2=
011 12:28 PM=0ATo: oracle-l@freelists.org=0ASubject: Re: ORA-01450: maximum=
 key length (3215) exceeded -- Can't create index online with an nvarchar2 =
column=0A=0A=0AI would enable sql_trace and look at the create statement pr=
oduced for the =0Ajournalling IOT.=0AYour 2011 is wrong, it should be 1,111=
; if (for no good reason) you multiply the =0Acolumn length by 3 (again), t=
he answer comes to 3217.=0A=0ASo, taking a guess, Oracle has taken the data=
 length as 3 x char_length, and =0Athen the journal code has defined it's c=
har_length as the result - which has =0Ataken ITS data_length to 9 times th=
e original char_length.=0A=0ARaise an SR if I'm right.=0A=0ARegards=0A=0AJo=
nathan Lewis=0Ahttp://jonathanlewis.wordpress.com=0A=0A=0A----- Original Me=
ssage ----- =0AFrom: "Kudryk, Mark" <Mark_Kudryk@ADP.com>=0ATo: <oracle-l@f=
reelists.org>=0ASent: Monday, August 29, 2011 5:05 PM=0ASubject: ORA-01450:=
 maximum key length (3215) exceeded -- Can't create index =0Aonline with an=
 nvarchar2 column=0A=0A=0ATABLE_NAME=A0 =A0 =A0 =A0 =A0 =A0 COLUMN_NAME=A0 =
=A0 =A0 =A0 =A0 =A0 DATA_TYPE=A0 =A0 =A0 DATA_LENGTH =0ACHAR_LENGTH=0A=0AMK=
_TEST_LENGTH_N351=A0 =A0 INVOICE_ID=A0 =A0 =A0 =A0 =A0 =A0 =A0 NUMBER=A0 =
=A0 =A0 =A0 =A0 =A0 22 =0A0=0A=0AMK_TEST_LENGTH_N351=A0 =A0 DATA_FIELD_ID=
=A0 =A0 =A0 =A0 =A0 =A0 NUMBER=A0 =A0 =A0 =A0 =A0 =A0 22 =0A0=0A=0AMK_TEST_=
LENGTH_N351=A0 =A0 ADDITIONAL_DATA_N351=A0 =A0 NVARCHAR2=A0 =A0 =A0 =A0 105=
3 =0A351=0A=0AUsing the formula above, I calculate the key length to be 105=
3 =0A(v_additional_data) + 22 (invoice_id) + 22 (data_field_id) + 3 (number=
 of =0Acolumns) + 2 (length of key) + 8 (ROWID) + 1 (length of rowid) =3D 2=
011, which is =0Anot close to 3215. As we are using UTF8, I would have expe=
cted that the number =0Aof nvarchar2 characters I could have used would hav=
e been 1/3 of a varchar2 =0Acolumn (3155 vs. 1031), and not approximately 1=
/9th (350) the size.=0A=0A=0A--=0Ahttp://www.freelists.org/webpage/oracle-l=
=0A=0A=0A------------------------------------------------------------------=
----=0AThis message and any attachments are intended only for the use of th=
e addressee and may contain information that is privileged and confidential=
. If the reader of the message is not the intended recipient or an authoriz=
ed representative of the intended recipient, you are hereby notified that a=
ny dissemination of this communication is strictly prohibited. If you have =
received this communication in error, notify the sender immediately by retu=
rn email and delete the message and any attachments from your system.=0A--=
=0Ahttp://www.freelists.org/webpage/oracle-l=0A=0A=0A--=0Ahttp://www.freeli=
sts.org/webpage/oracle-l
--0-1770168762-1314649842=:15352
Content-Type: text/html; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

<html><body><div style=3D"color:#000; background-color:#fff; font-family:ga=
ramond, new york, times, serif;font-size:12pt"><div style=3D"RIGHT: auto"><=
SPAN style=3D"RIGHT: auto">Before you do check how NLS_LENGTH_SEMANTICS is =
set; it may be set for CHAR rather than BYTE.&nbsp; If it's set correctly (=
BYTE) and this still occurs submit an SR.<VAR id=3Dyui-ie-cursor></VAR><BR =
style=3D"RIGHT: auto" class=3Dyui-cursor></SPAN></div>
<DIV></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT color=3D#0060bf face=3D"arial black">David Fitzjarrell</FONT></D=
IV>
<DIV>&nbsp;</DIV>
<div><BR></div>
<DIV style=3D"FONT-FAMILY: garamond, new york, times, serif; FONT-SIZE: 12p=
t">
<DIV style=3D"FONT-FAMILY: times new roman, new york, times, serif; FONT-SI=
ZE: 12pt"><FONT size=3D2 face=3DArial>
<DIV style=3D"BORDER-BOTTOM: #ccc 1px solid; BORDER-LEFT: #ccc 1px solid; P=
ADDING-BOTTOM: 0px; LINE-HEIGHT: 0; MARGIN: 5px 0px; PADDING-LEFT: 0px; PAD=
DING-RIGHT: 0px; HEIGHT: 0px; FONT-SIZE: 0px; BORDER-TOP: #ccc 1px solid; B=
ORDER-RIGHT: #ccc 1px solid; PADDING-TOP: 0px" class=3Dhr readonly=3D"true"=
 contenteditable=3D"false"></DIV><B><SPAN style=3D"FONT-WEIGHT: bold">From:=
</SPAN></B> "Kudryk, Mark" &lt;Mark_Kudryk@ADP.com&gt;<BR><B><SPAN style=3D=
"FONT-WEIGHT: bold">To:</SPAN></B> "oracle-l@freelists.org" &lt;oracle-l@fr=
eelists.org&gt;<BR><B><SPAN style=3D"FONT-WEIGHT: bold">Sent:</SPAN></B> Mo=
nday, August 29, 2011 1:08 PM<BR><B><SPAN style=3D"FONT-WEIGHT: bold">Subje=
ct:</SPAN></B> RE: ORA-01450: maximum key length (3215) exceeded -- Can't c=
reate index online with an nvarchar2 column<BR></FONT><BR>I've looked at th=
e trace.&nbsp; Here's the portion where the index is being
 created...<BR><BR>********************************************************=
************************<BR><BR>create index MK_INDEX_NVARCHAR_N351 ON mk_t=
est_length_n351 (data_field_id, <BR>&nbsp; UPPER(additional_data_n351), inv=
oice_id) tablespace indx online<BR><BR><BR>call&nbsp; &nbsp; count&nbsp; &n=
bsp; &nbsp; cpu&nbsp; &nbsp; elapsed&nbsp; &nbsp; &nbsp; disk&nbsp; &nbsp; =
&nbsp; query&nbsp; &nbsp; current&nbsp; &nbsp; &nbsp; &nbsp; rows<BR>------=
- ------&nbsp; -------- ---------- ---------- ---------- ----------&nbsp; -=
---------<BR>Parse&nbsp; &nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 0.01&nb=
sp; &nbsp; &nbsp; 0.02&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nb=
sp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp;=
 &nbsp; &nbsp; 0<BR>Execute&nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 0.02&=
nbsp; &nbsp; &nbsp; 0.01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &=
nbsp; &nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; &nbsp; 27&nbsp; &nbsp;
 &nbsp; &nbsp; &nbsp; 0<BR>Fetch&nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; =
&nbsp; 0.00&nbsp; &nbsp; &nbsp; 0.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nb=
sp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp;=
 &nbsp; &nbsp; &nbsp; &nbsp; 0<BR>------- ------&nbsp; -------- ---------- =
---------- ---------- ----------&nbsp; ----------<BR>total&nbsp; &nbsp; &nb=
sp; &nbsp; 2&nbsp; &nbsp; &nbsp; 0.03&nbsp; &nbsp; &nbsp; 0.03&nbsp; &nbsp;=
 &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &n=
bsp; &nbsp; 27&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0<BR><BR>Misses in library=
 cache during parse: 1<BR>Optimizer mode: FIRST_ROWS<BR>Parsing user id: 12=
1&nbsp; <BR>***************************************************************=
*****************<BR><BR>And here is the portion where the IOT is being cre=
ated.<BR><BR>**************************************************************=
******************<BR><BR>create table
 "DOCPADMIN"."SYS_JOURNAL_425558" (C0 NUMBER, C1 NVARCHAR2(1053), <BR>&nbsp=
; C2 NUMBER,&nbsp; opcode char(1), partno number,&nbsp; rid rowid, primary =
key( C0, C1, <BR>&nbsp; C2 , rid )) organization index TABLESPACE "INDX"<BR=
><BR><BR>call&nbsp; &nbsp; count&nbsp; &nbsp; &nbsp; cpu&nbsp; &nbsp; elaps=
ed&nbsp; &nbsp; &nbsp; disk&nbsp; &nbsp; &nbsp; query&nbsp; &nbsp; current&=
nbsp; &nbsp; &nbsp; &nbsp; rows<BR>------- ------&nbsp; -------- ----------=
 ---------- ---------- ----------&nbsp; ----------<BR>Parse&nbsp; &nbsp; &n=
bsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 0.02&nbsp; &nbsp; &nbsp; 0.00&nbsp; &nbsp=
; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &=
nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0<BR>Execute&nbsp; =
&nbsp; &nbsp; 1&nbsp; &nbsp; &nbsp; 0.01&nbsp; &nbsp; &nbsp; 0.01&nbsp; &nb=
sp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp;=
 &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
 0<BR>Fetch&nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; 0.00&nbsp; &nb=
sp; &nbsp; 0.00&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nb=
sp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp;=
 &nbsp; 0<BR>------- ------&nbsp; -------- ---------- ---------- ----------=
 ----------&nbsp; ----------<BR>total&nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &n=
bsp; &nbsp; 0.03&nbsp; &nbsp; &nbsp; 0.01&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;=
 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&=
nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0<BR><BR>Misses in library cache during p=
arse: 1<BR>Optimizer mode: FIRST_ROWS<BR>Parsing user id: 121&nbsp; &nbsp; =
(recursive depth: 1)<BR>***************************************************=
*****************************<BR><BR>Initially when I read this, I assumed =
the NVARCHAR2(1053) was defined in bytes (351 * 3).&nbsp; But it's not... i=
t's in characters, the data length of this field in the IOT will
 actually be 3159.&nbsp; Plugging this value into the formula, I get an ove=
rall length of 3217, which exceeds the limit.<BR><BR>I'll raise an SR.<BR><=
BR>Mark<BR><BR>-----Original Message-----<BR>From: <A href=3D"mailto:oracle=
-l-bounce@freelists.org" ymailto=3D"mailto:oracle-l-bounce@freelists.org">o=
racle-l-bounce@freelists.org</A> [mailto:<A href=3D"mailto:oracle-l-bounce@=
freelists.org" ymailto=3D"mailto:oracle-l-bounce@freelists.org">oracle-l-bo=
unce@freelists.org</A>] On Behalf Of Kudryk, Mark (ES)<BR>Sent: Monday, Aug=
ust 29, 2011 1:54 PM<BR>To: <A href=3D"mailto:oracle-l@freelists.org" ymail=
to=3D"mailto:oracle-l@freelists.org">oracle-l@freelists.org</A><BR>Subject:=
 RE: ORA-01450: maximum key length (3215) exceeded -- Can't create index on=
line with an nvarchar2 column<BR><BR>Thanks. Mea Culpa on the length.<BR><B=
R>-----Original Message-----<BR>From: <A href=3D"mailto:oracle-l-bounce@fre=
elists.org"
 ymailto=3D"mailto:oracle-l-bounce@freelists.org">oracle-l-bounce@freelists=
.org</A> [mailto:<A href=3D"mailto:oracle-l-bounce@freelists.org" ymailto=
=3D"mailto:oracle-l-bounce@freelists.org">oracle-l-bounce@freelists.org</A>=
] On Behalf Of Jonathan Lewis<BR>Sent: Monday, August 29, 2011 12:28 PM<BR>=
To: <A href=3D"mailto:oracle-l@freelists.org" ymailto=3D"mailto:oracle-l@fr=
eelists.org">oracle-l@freelists.org</A><BR>Subject: Re: ORA-01450: maximum =
key length (3215) exceeded -- Can't create index online with an nvarchar2 c=
olumn<BR><BR><BR>I would enable sql_trace and look at the create statement =
produced for the <BR>journalling IOT.<BR>Your 2011 is wrong, it should be 1=
,111; if (for no good reason) you multiply the <BR>column length by 3 (agai=
n), the answer comes to 3217.<BR><BR>So, taking a guess, Oracle has taken t=
he data length as 3 x char_length, and <BR>then the journal code has define=
d it's char_length as the result - which has <BR>taken ITS data_length to 9
 times the original char_length.<BR><BR>Raise an SR if I'm right.<BR><BR>Re=
gards<BR><BR>Jonathan Lewis<BR><A href=3D"http://jonathanlewis.wordpress.co=
m/" target=3D_blank>http://jonathanlewis.wordpress.com</A><BR><BR><BR>-----=
 Original Message ----- <BR>From: "Kudryk, Mark" &lt;<A href=3D"mailto:Mark=
_Kudryk@ADP.com" ymailto=3D"mailto:Mark_Kudryk@ADP.com">Mark_Kudryk@ADP.com=
</A>&gt;<BR>To: &lt;<A href=3D"mailto:oracle-l@freelists.org" ymailto=3D"ma=
ilto:oracle-l@freelists.org">oracle-l@freelists.org</A>&gt;<BR>Sent: Monday=
, August 29, 2011 5:05 PM<BR>Subject: ORA-01450: maximum key length (3215) =
exceeded -- Can't create index <BR>online with an nvarchar2 column<BR><BR><=
BR>TABLE_NAME&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; COLUMN_NAME&nbsp; &n=
bsp; &nbsp; &nbsp; &nbsp; &nbsp; DATA_TYPE&nbsp; &nbsp; &nbsp; DATA_LENGTH =
<BR>CHAR_LENGTH<BR><BR>MK_TEST_LENGTH_N351&nbsp; &nbsp; INVOICE_ID&nbsp; &n=
bsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NUMBER&nbsp; &nbsp; &nbsp; &nbsp; &=
nbsp;
 &nbsp; 22 <BR>0<BR><BR>MK_TEST_LENGTH_N351&nbsp; &nbsp; DATA_FIELD_ID&nbsp=
; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; NUMBER&nbsp; &nbsp; &nbsp; &nbsp; &nbs=
p; &nbsp; 22 <BR>0<BR><BR>MK_TEST_LENGTH_N351&nbsp; &nbsp; ADDITIONAL_DATA_=
N351&nbsp; &nbsp; NVARCHAR2&nbsp; &nbsp; &nbsp; &nbsp; 1053 <BR>351<BR><BR>=
Using the formula above, I calculate the key length to be 1053 <BR>(v_addit=
ional_data) + 22 (invoice_id) + 22 (data_field_id) + 3 (number of <BR>colum=
ns) + 2 (length of key) + 8 (ROWID) + 1 (length of rowid) =3D 2011, which i=
s <BR>not close to 3215. As we are using UTF8, I would have expected that t=
he number <BR>of nvarchar2 characters I could have used would have been 1/3=
 of a varchar2 <BR>column (3155 vs. 1031), and not approximately 1/9th (350=
) the size.<BR><BR><BR>--<BR><A href=3D"http://www.freelists.org/webpage/or=
acle-l"
 target=3D_blank>http://www.freelists.org/webpage/oracle-l</A><BR><BR><BR>-=
---------------------------------------------------------------------<BR>Th=
is message and any attachments are intended only for the use of the address=
ee and may contain information that is privileged and confidential. If the =
reader of the message is not the intended recipient or an authorized repres=
entative of the intended recipient, you are hereby notified that any dissem=
ination of this communication is strictly prohibited. If you have received =
this communication in error, notify the sender immediately by return email =
and delete the message and any attachments from your system.<BR>--<BR><A hr=
ef=3D"http://www.freelists.org/webpage/oracle-l" target=3D_blank>http://www=
.freelists.org/webpage/oracle-l</A><BR><BR><BR>--<BR><A href=3D"http://www.=
freelists.org/webpage/oracle-l" target=3D_blank>http://www.freelists.org/we=
bpage/oracle-l</A><BR><BR><BR><BR><BR></DIV></DIV></div></body></html>
--0-1770168762-1314649842=:15352--
--
http://www.freelists.org/webpage/oracle-l


