varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version
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
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
Received on Wed Dec 16 2020 - 15:56:22 CET