MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL. There are 2 possible values of this parameter:
- MAX_STRING_SIZE = STANDARD means that the length limits for Oracle Database releases prior to Oracle Database 12c apply (for example, 4000 bytes for VARCHAR2 and NVARCHAR2, and 2000 bytes for RAW).
- MAX_STRING_SIZE = EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.
You can change the value of MAX_STRING_SIZE from STANDARD to EXTENDED. However, you cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.
Example of how to set MAX_STRING_SIZE = EXTENDED
For non-CDB:
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a non-CDB:
1. Shut down the database.
2. Restart the database in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script. You must be connected AS SYSDBA to run the script.
For non-CDB:
SHUTDOWN IMMEDIATE; STARTUP UPGRADE; alter system set MAX_STRING_SIZE=EXTENDED; @?/rdbms/admin/utl32k.sql SHUTDOWN IMMEDIATE; STARTUP;
For RAC system:
alter system set cluster_database = false scope = spfile; SHUTDOWN IMMEDIATE; --- shutdown all the instances STARTUP UPGRADE; alter system set MAX_STRING_SIZE=EXTENDED; @?/rdbms/admin/utl32k.sql alter system set cluster_database = true scope = spfile; SHUTDOWN IMMEDIATE; STARTUP --- or you can use srvctl to start the database.
For PDB:
To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a PDB:
1. Shut down the PDB.
2. Restart the PDB in UPGRADE mode.
3. Change the setting of MAX_STRING_SIZE in the PDB to EXTENDED.
4. Run the rdbms/admin/utl32k.sql script in the PDB. You must be connected AS SYSDBA to run the utl32k.sql script.
5. Restart the PDB in NORMAL mode.
6. Restart the database in NORMAL mode.