MAX_STRING_SIZE Parameter In Oracle 12c:

Parameter "MAX_STRING_SIZE" is introduced in Oracle 12c.This controls the maximum size of string size in Oracle database.Parameter value either can be set to  STANDARD or EXTENDED
Default value is STANDARD.

MAX_STRING_SIZE = STANDARD means the maximum size of strings is 4000 bytes for VARCHAR2 and NVARCHAR2.

MAX_STRING_SIZE = EXTENDED means maximum size can be upto 32767 byte .

We can change the parameter value from STANDARD to EXTENDED but not possible from EXTENDED to STANDARD.


If MAX_STRING_SIZE value set to STANDARD and  try to set the length of column more than 4000,will return ORA-00910.

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD


SQL> create table country ( state_name VARCHAR2(7000));
create table country ( state_name VARCHAR2(7000))
                                                *
ERROR at line 1:
ORA-00910: specified length too long for its datatype

This is how we can convert MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode:

SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL>STARTUP UPGRADE

ORACLE instance started.
Total System Global Area 900000 bytes
Fixed Size 666 bytes
Variable Size 271790 bytes
Database Buffers 271790 bytes
Redo Buffers 4140 bytes
Database mounted.
Database opened.

2. Change the value to EXTENDED

SQL>  alter system set MAX_STRING_SIZE='EXTENDED' SCOPE=BOTH;

System altered.

SQL> show parameter MAX_STRING_SIZE

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

3.Run script utl32k.sql :

@?/rdbms/admin/utl32k.sql

4.Restart the database:

shutdown immediate;
startup
SQL> show parameter MAX_STRING_SIZE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

Know Issues:

1. If you try to restart the database without running utl32k.sql script, got below error.

Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-14695: MAX_STRING_SIZE migration is incomplete
Process ID: 18669
Session ID: 401 Serial number: 16419

So start the database again in UPGRADE mode and execute utlk32.sql script.

2. If you try to set the value to EXTENDED , when database is not in UPGRADE mode.


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-14694: database must in UPGRADE mode to begin MAX_STRING_SIZE migration

before running this alter statement, need to start database in UPGRADE mode.