AW: [EXT] Re: varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle version

Happy new year,

sorry for late reply.

No, I didn't change it.
max_string_size = STANDARD on my 19c database.

Just can guess:
Maybe the error occurs because the maximum key length COULD exceed with maximum_string_size=EXTENDED, even though your CURRENT column definition doesn't exceed the limit... just thinking of "alter table modify column"...

Regards.

Manfred

> -----Ursprüngliche Nachricht-----
> Von: Thorsten Bruhns <thorsten.bruhns_at_googlemail.com>
> Gesendet: Donnerstag, 24. Dezember 2020 14:35
> An: Pausch Manfred <Manfred.Pausch_at_tgw-group.com>
> Cc: Oracle Mailing List <oracle-l_at_freelists.org>
> Betreff: [EXT] Re: varchar2 DATA_LENGTH vs. NLS_CHARACTERSET vs. Oracle
> version
>
> 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

†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Thu Jan 07 2021 - 17:37:49 CET