This parameter was introduced in Oracle 12c. MAX_STRING_SIZE controls the maximum size of string size in Oracle database.
Either we can set it to STANDARD or.EXTENDED The 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 value of MAX_STRING_SIZE from STANDARD to EXTENDED. But not from EXTENDED to STANDARD.

With MAX_STRING_SIZE set to STANDARD , if we try to set the length of column more than 4000, then it will throw ORA-00910 error.

SQL> SHOW PARAMETER MAX_STRING_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD
SQL>


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

STEPS for converting MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode:

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

SQL>STARTUP UPGRADE

ORACLE instance started.

Total System Global Area 1.4663E+10 bytes
Fixed Size 15697000 bytes
Variable Size 1.1878E+10 bytes
Database Buffers 2717908992 bytes
Redo Buffers 51404800 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 utl32k.sql script:

@?/rdbms/admin/utl32k.sql

SQL> @?/rdbms/admin/utl32k.sql

Session altered.




DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database has not been opened for UPGRADE.
DOC>
DOC>   Perform a "SHUTDOWN ABORT"  and
DOC>   restart using UPGRADE.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if the database does not have compatible >= 12.0.0
DOC>
DOC>   Set compatible >= 12.0.0 and retry.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.


Session altered.


1524 rows updated.


Commit complete.


System altered.


PL/SQL procedure successfully completed.


Commit complete.


System altered.


Session altered.


Session altered.


Table created.


Table created.


Table created.


Table truncated.


0 rows created.


PL/SQL procedure successfully completed.

no rows selected

DOC>#######################################################################
DOC>#######################################################################
DOC>   The following statement will cause an "ORA-01722: invalid number"
DOC>   error if we encountered an error while modifying a column to
DOC>   account for data type length change as a result of enabling or
DOC>   disabling 32k types.
DOC>
DOC>   Contact Oracle support for assistance.
DOC>#######################################################################
DOC>#######################################################################
DOC>#

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Commit complete.

4.Restart the database:

shutdown immediate;
startup


SQL> show parameter max_string

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

Let’s create a table with length more than 4000.

SQL> create table UNIVERSITY ( COLLEGE_NAME VARCHAR2(8000));

Table created.

COMMON ISSUES:

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

Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1.4663E+10 bytes
Fixed Size                 15697000 bytes
Variable Size            1.1878E+10 bytes
Database Buffers         2717908992 bytes
Redo Buffers               51404800 bytes
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

So before running this alter statement, start the database in UPGRADE mode.

CONCLUSION: Once the max_string_size is changed to EXTENDED,We can not  revert to original STANDARD value.So before changing the value in critical or production database, Please do thorough testing , As you may hit uninvited bugs.

 

NOTE: As suggested by one Mark in comment section:

————————————————————-

Thanks for this.

One other thing you might want to add:
If you have items in the recyclebin when you run utl32k.sql, you’ll get:
DECLARE
*
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin
ORA-06512: at line 121

Disconnected from Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

Simple fix, purge recyclebin and re-run utl32k.sql:
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 13:24:55 2020

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

Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 – 64bit Production
With the Automatic Storage Management option

SQL> purge dba_recyclebin;
@?/rdbms/admin/utl32k

-Mark

—————————————————-