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.

STEPS for converting MAX_STRING_SIZE to EXTENDED:

1. Start database in upgrade mode:

2. Change the value to EXTENDED

3.Run utl32k.sql script:

@?/rdbms/admin/utl32k.sql

4.Restart the database:

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

COMMON ISSUES:

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

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.

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

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