Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Jean-Philippe.

Asked: August 05, 2022 - 9:11 am UTC

Last updated: August 10, 2022 - 3:22 am UTC

Version: 21c

Viewed 100+ times

You Asked

Hello,
I inherited from a schema that is using nvarchar2.
I am trying to recreate that schema but I am struggling to create table with nvarchar2 that have a size of 4000 (> 2000).
I have changed the max_string_size to "extended" but it does not works. I works varchar2 with e.g. 32676 bytes.

Here are the operation I made.
sqlplus / as sysdba
ALTER SESSION SET CONTAINER=CDB$ROOT;
ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;
shutdown immediate;
startup upgrade;
ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
EXIT;
cd $ORACLE_HOME/rdbms/admin
mkdir -p /home/oracle/tmp/log_utl32k
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/log_utl32k' -b utl32k_cdb_pdbs_output utl32k.sql
sqlplus / as sysdba
shutdown immediate;
startup;
ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE;
EXIT;
mkdir -p /home/oracle/tmp/utlrp
$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/utlrp' -b utlrp_cdb_pdbs_output utlrp.sql
sqlplus / as sysdba
create table testvc (name varchar2(32676));
>>Table created.
create table testnvc (name nvarchar2(4000));
>>ORA-00910: specified length too long for its datatype


I have checked several resources but I cannot find a solution.
Any suggestion are welcomed.

and Chris said...

You appear to be connected to the root container as SYS. Do not try to create objects in SYS! It's special. You should also avoid creating objects in the root container.

What happens if you connect to a PDB as a regular database user?

Rating

  (13 ratings)

Comments

A reader, August 05, 2022 - 1:50 pm UTC

Hello, Indeed I should not create object with SYS.
I have restarted from a fresh image (Docker)
And restart step-by-step.
But this time, I have created a new schema (with SYSTEM).
ALTER SESSION SET "_ORACLE_SCRIPT"= true;
CREATE TABLESPACE MYUSR DATAFILE '/opt/oracle/oradata/ORCLCDB/MYUSR' SIZE 100 M AUTOEXTEND ON NEXT 100 M MAXSIZE 10 G;
CREATE USER MYUSR IDENTIFIED BY "eqntqgiwxp1$" DEFAULT TABLESPACE MYUSR;
GRANT CONNECT, RESOURCE TO MYUSR;
GRANT UNLIMITED TABLESPACE TO MYUSR;
GRANT CREATE VIEW TO MYUSR;

And when connected as MYUSR I try to create the tables I have the error for both types.
SQL> create table testvc (name varchar2(32676));
create table testvc (name varchar2(32676))
                                   *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> create table testnvc (name nvarchar2(4000));
create table testnvc (name nvarchar2(4000))
                                          *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


Connor McDonald
August 08, 2022 - 1:28 am UTC

https://connor-mcdonald.com/2015/09/11/longer-strings-in-12c/

you need to run the appropriate scripts

To Jean-Phillipe

J. Laurindo Chiappa, August 05, 2022 - 2:46 pm UTC

A reader, August 05, 2022 - 3:30 pm UTC

Hi J. Laurindo Chiappa,
Base on https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/MAX_STRING_SIZE.html#GUID-D424D23B-0933-425F-BC69-9C0E6724693C what I understood is that the "The --force_pdb_mode ‘UPGRADE’ option is used to ensure that all PDBs, including application root clones, are opened in migrate mode" so all PDBs should be upgraded too...
Connor McDonald
August 08, 2022 - 1:27 am UTC

There is a difference between opening a pdb in migrate mode and ensuring that the appropriate maz_size scripts are subsequently run

To "A reader"

J. Laurindo Chiappa, August 05, 2022 - 4:15 pm UTC

yep, it was exactly what was done in the article : let's see if it WAS the issue for Jean-Philippe., or not....

A reader, August 05, 2022 - 8:53 pm UTC

create the table with 32K It will work. Hope it helps 32637 is too high that is why it is failing
Connor McDonald
August 08, 2022 - 1:25 am UTC

32637 is fine

To "A reder#2"

J. Laurindo Chiappa, August 06, 2022 - 11:36 pm UTC

No, sir : as https://www.gbmb.org/kb-to-bytes show to us, 32 KB are 32768 bytes - due to the string-ending CHR(0) eventually present, the real maximum limit isn't 32768 but 32767 :

APP_OWNER@db_app::CNTNR=DB_APP> create table testvc2 (name varchar2(32768));
create table testvc2 (name varchar2(32768))
*
ERROR in line 1:
ORA-00910: specified length too long for its datatype


APP_OWNER@db_app::CNTNR=DB_APP> create table testvc2 (name varchar2(32767));

Table created.

APP_OWNER@db_app::CNTNR=DB_APP> @desc testvc2
Nome Nulo? Tipo
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(32767)

===> 32637 is LESS THAN 32767 , so it works, too :

APP_OWNER@db_app::CNTNR=DB_APP> create table testvc3 (name varchar2(32637));

Table created.

APP_OWNER@db_app::CNTNR=DB_APP>

SHowing the "code"...

J. Laurindo Chiappa, August 06, 2022 - 11:43 pm UTC

https://pastebin.com/BzjDkivL is a pastebin with the step by step applying the 32k script on CDB and all of my PDBs and thus being able to create 32k strings, absolutely an easy task....
Connor McDonald
August 08, 2022 - 1:24 am UTC

A reader, August 08, 2022 - 9:28 am UTC

I tried the pastbin.
Despite that the parameter seems to be well set, it does not work.

The schema I create is still receiving the error...

Just for the information this is the version I am working with.
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

To "A reader

J. Laurindo Chiappa, August 08, 2022 - 10:24 am UTC

if you have followed the instructions contained in pastebin and it still doesn't work, there is no other alternative but to open a Support Ticket : please include on the Ticket the output of the 32k script (for ALL the PDBs AND for root container) and the messages in alert.log.

Regards,

J. Laurindo Chiappa

A reader, August 08, 2022 - 11:32 am UTC

Hello,
I just made another test, I have added the -initParams max_string_size=EXTENDED in the command line when creating the database.


sh-4.2$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 8 11:23:39 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> show parameter max_string_size;

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
max_string_size        string  EXTENDED


So I connected as system (sqlplus system) and created the schema MYUSR.

But it is not working either I sill have the errors

sh-4.2$ sqlplus MYUSR

SQL*Plus: Release 21.0.0.0.0 - Production on Mon Aug 8 11:25:11 2022
Version 21.3.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> create table testvc (name varchar2(32676));
create table testvc (name varchar2(32676))
                                   *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> create table testnvc (name nvarchar2(4000));
create table testnvc (name nvarchar2(4000))
                                          *
ERROR at line 1:
ORA-00910: specified length too long for its datatype



Very strange that even with an fresh installation which by default is set as "extended" it does not work.

To "A reader"

J. Laurindo Chiappa, August 08, 2022 - 12:12 pm UTC

First, just saying, it's not enough just "add the parameter max_string_size=EXTENDED" in CDB and in all of the PDBs (INCLUDING PDB$SEED , please) : you really Need beside that to run (AFTER a database restart) the utl32k.sql script in CDB AND in all PDBs , AND the script must finish WITHOUT ANY ERRORS, specially ORA-01722, AND the COMPATIBLE parameter in CDN AND in ALL of the PDBs must be set to (minumim) 12.1.xxx , or greater...
AND, really, this kind of error :

create table testnvc (name nvarchar2(4000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

==> really leads us to think about non-complete tasks : it looks like even the "native" limit of 4000 bytes is broken...

IF you did it ALL, don't forgeting ANY step, in the RIGHT order and without receiving ANY errors, all I can say is , again, please open a Support ticket, much probably you have some kind of bug in your hands....
Chris Saxon
August 08, 2022 - 1:24 pm UTC

Agreed - at this point it looks like something's broken and this needs to be taken up with Support.

A reader, August 09, 2022 - 8:13 am UTC

Hello,
I have tried again to validate that I have followed ALL the STEPS, in the RIGHT ORDER as provided.
I have also tried after creating the schema MYUSR before the update.
No success...
I have also tried with a 19c (still docker)
Following the documentation: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/MAX_STRING_SIZE.html#GUID-D424D23B-0933-425F-BC69-9C0E6724693C
And also with the steps provided here.
No success...
Would it be linked to the docker implementation?
Chris Saxon
August 09, 2022 - 1:04 pm UTC

As stated, if:

- You're following all the steps as documented
- These report no errors
- It's still not working
=> you need to contact Support

https://support.oracle.com

Ask TOM is NOT Support.

Docs

emaN, August 10, 2022 - 12:54 am UTC

Nvarchar2
The maximum value is:
16383 if MAX_STRING_SIZE = EXTENDED and the national character set is AL16UTF16
32767 if MAX_STRING_SIZE = EXTENDED and the national character set is UTF8
2000 if MAX_STRING_SIZE = STANDARD and the national character set is AL16UTF16
4000 if MAX_STRING_SIZE = STANDARD and the national character set is UTF8

Connor McDonald
August 10, 2022 - 3:22 am UTC

good input.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.