The Geek Diary

How to change max_string_size value from STANDARD to EXTENDED

by

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

Note: database vault should be disabled before performing the steps.

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;
Note: If using pfile, ensure that max_string_size=extended is added to pfile before a normal startup is attempted after executing the utl32k.sql script.

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.

Note: If you are using a cluster you first have to disable it using cluster_database=False and then start the DB in startup upgrade mode. PDB$SEED should be upgraded as well.

You May Also Like