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@xxxxxxxxxxxxxx>
Gesendet: Donnerstag, 24. Dezember 2020 14:35
An: Pausch Manfred <Manfred.Pausch@xxxxxxxxxxxxx>
Cc: Oracle Mailing List <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxxxxxx>:
maximum key length (6398).
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
databases?
But why the data_length for a "varchar2(xxx CHAR)" column is different on
both
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
