varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version

From: Pausch Manfred <Manfred.Pausch_at_tgw-group.com>
Date: Wed, 16 Dec 2020 14:56:22 +0000
Message-ID: <AM7P191MB1121128699BE935277D14CACC6C50_at_AM7P191MB1121.EURP191.PROD.OUTLOOK.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
i0zX+n{+i^ Received on Wed Dec 16 2020 - 15:56:22 CET

Original text of this message