Re: varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version

From: Thorsten Bruhns <"Thorsten>
Date: Thu, 24 Dec 2020 14:34:34 +0100
Message-ID: <CAAw07oZv+4himBFCXjVk5JBf86t3AVh5Jix7Hnu3WF-VH=7DhA_at_mail.gmail.com>


Hi,
did you change the parameter max_string_size in 19c? I have the same error, when max_string_size=exetended with the following testcase:

create table tab2(v varchar2(2000 char)); create index i2 on tab2(v);

I use the same characterset. The error is not raised when max_string_size is set to standard.

Kind Regards
Thorsten Bruhns

Am Mi., 16. Dez. 2020 um 15:58 Uhr schrieb Pausch Manfred <Manfred.Pausch_at_tgw-group.com>:
>
> Hello all,
>
> have 2 databases. One is 11.2.0.4, the other is 19c.
> (yes, it's time for upgrading to 19c *g*)
>
>
> 11.2 has NLS_CHARACTERSET = UTF8
> 19c has NLS_CHARACTERSET = AL32UTF8
>
> On both DBs I have a table
> CREATE TABLE mytable
> ( "PRESETNUMBERCONFIGPRJID" NUMBER(12,0),
> "PRESETNUMBER" NUMBER(12,0),
> "SHELLSIZE" VARCHAR2(1000 CHAR),
> "FILMTYPE" VARCHAR2(1000 CHAR),
> <some other insignificant columns>
> )
>
>
>
> On 11.2 I have an index (because of a unique constraint):
> CREATE UNIQUE INDEX myindex ON mytable ("SHELLSIZE", "FILMTYPE") ;
>
> On 19c creation of the same index fails with:
> ORA-01450: maximum key length (6398) exceeded
>
> Tablespace blocksize on both databases is 8k.
>
>
> Querying USER_TAB_COLS with
> SELECT data_type, data_length, char_col_decl_length, char_length, char_used
> FROM user_tab_cols
> WHERE table_name = mytable
> AND column_name in ('SHELLSIZE', 'FILMTYPE');
>
> Returns
> On 11.2:
> DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
> ---------- ----------- -------------------- ----------- -
> VARCHAR2 3000 3000 1000 C
> VARCHAR2 3000 3000 1000 C
>
>
> And on 19c:
> DATA_TYPE DATA_LENGTH CHAR_COL_DECL_LENGTH CHAR_LENGTH C
> ---------- ----------- -------------------- ----------- -
> VARCHAR2 4000 4000 1000 C
> VARCHAR2 4000 4000 1000 C
>
>
> As I understand index creation on 19c (NLS_CHARACTERSET = AL32UTF8) fails because
> data_length of both columns (4000+4000) is more than the maximum key length (6398).
>
> But why the data_length for a "varchar2(xxx CHAR)" column is different on both databases?
> Has it to do with the different NLS_CHARACTERSET settings?
> Or with the different database versions?
> Or something totally different?
>
> As far as I know UTF8 encodings are variable-width, which use up to 4bytes (depending on the character you want to store...)
>
> So maybe someone could shed some light, please :-)
>
>
> Thanks in advance
> Manfred

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Dec 24 2020 - 14:34:34 CET

Original text of this message