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@xxxxxxxxxxxxx>:
--
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
