0

New to Oracle and SQL.
I have already followed normal procedure to allow for this size:

startup upgrade
alter system set MAX_STRING_SIZE ='EXTENDED' scope=spfile;
@%ORACLE_HOME%\RDBMS\ADMIN\utl32k.sql
@%ORACLE_HOME%\RDBMS\ADMIN\utlrp.sql
shutdown immediate
startup;

No error messages during sql scripts or startup. Tried to create table column in user but still getting error below:

SQL> CONN oracle
<password>
SQL> CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
CREATE TABLE UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

SQL> SHOW PARAMETER MAX_STRING_SIZE

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

Please assist me in finding my error.

@Balazs: I have attempted your commands but receive errors:

$ sqlplus /nolog

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jun 18 08:21:34 2019

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

SQL> show parameter max_string_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED
SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));
create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype


SQL> CONN sys / as sysdba
Enter password:
Connected.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.

Total System Global Area 5033164800 bytes
Fixed Size                  8803120 bytes
Variable Size            1040190672 bytes
Database Buffers         3976200192 bytes
Redo Buffers                7970816 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified


SQL>

Not sure where I have gone wrong.

closed as off-topic by Balazs Papp, Paul White Jul 5 at 6:35

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Balazs Papp, Paul White
If this question can be reworded to fit the rules in the help center, please edit the question.

1

The reason for that behaviour is that you are in a multi-tenant environment, i.e. a master container called the CDB ("Container Database"), and any number of PDBs ("Pluggable Databases").

The CDB ("container") is a kind of "system" database that is there to contain the actual customer databases ("pluggable databases" or PDBs). The CDB is not intended to receive any customer data whatsoever. Everything goes into one or more PDBs.

When you connect without specifying any service, you are automatically placed in the CDB. The extended strings parameter is ignored for the CDB: the limit remains 4000 bytes. The following connects to the CDB. Creating a table with a long string is rejected, just like in your case:

$ sqlplus system/oracle
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 20 16:05:35 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Tue Jun 18 2019 15:23:31 +02:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create table t1 (s varchar2(4000));
Table created.

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

Since you did a starter installation, you should just have one PDB created initially, called ORCLPDB1. You can find what PDBs you have by doing this (in the CDB). This is what I have (also a default installation):

SQL> select name from v$containers;

NAME
-------------------------------------------------------------------------------
CDB$ROOT
PDB$SEED
ORCLPDB1

3 rows selected.

CDB$ROOT is the container itself. PDB$SEED is a template database used when you create a new PDB. And ORCLPDB1 is my (one and only PDB).

$ sqlplus system/oracle@orclpdb1
SQL*Plus: Release 18.0.0.0.0 - Production on Thu Jun 20 16:20:04 2019
Version 18.3.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Last Successful login time: Thu Jun 20 2019 16:05:35 +02:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.3.0.0.0

SQL> create table t1 (s varchar2(4000));
Table created.

SQL> create table t2 (s varchar2(8000));
Table created.

So: you must always connect to the proper PDB. This is where you create your schemas, your tables, etc. Also you must create those tables in your own schema(s). You never create any user data in any of the "system" schemas (system, etc).

1

Those are not the correct steps to enable the extended data type. You have set max_string_size to EXTENDED in the spfile (scope=spfile) so it had no effect while running the scripts.

Try again.

Non-CDB:

Enabling the New Extended Data Type Capability

CONNNECT SYS / AS SYSDBA
SHUTDOWN IMMEDIATE;
STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
START $ORACLE_HOME/rdbms/admin/utl32k.sql
SHUTDOWN IMMEDIATE;
STARTUP;

Or:

MAX_STRING_SIZE

Not the answer you're looking for? Browse other questions tagged or ask your own question.