MAX_STRING_SIZE

MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

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).

EXTENDED means that the 32767 byte limit introduced in Oracle Database 12c applies.

The COMPATIBLE initialization parameter must be set to 12.0.0.0 or higher to set MAX_STRING_SIZE = EXTENDED.

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.

By setting MAX_STRING_SIZE = EXTENDED, users are taking an explicit action that could introduce application incompatibility in their database. Applications that do not want to use the expanded data types can be rewritten for compatibility with either setting; for example, these applications could use explicit CASTs to fix the length of VARCHAR2 expressions during CREATE TABLE AS SELECT.

Altering MAX_STRING_SIZE will update database objects and possibly invalidate them, as follows:

• Tables with virtual columns will be updated with new data type metadata for virtual columns of VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) type.

  • Functional indexes will become unusable if a change to their associated virtual columns causes the index key to exceed index key length limits. Attempts to rebuild such indexes will fail with ORA-01450: maximum key length exceeded.

• Views will be invalidated if they contain VARCHAR2(4000), 4000-byte NVARCHAR2, or RAW(2000) typed expression columns.

• Materialized views will be updated with new metadata VARCHAR2(4000), 4000-byte NVARCHAR2, and RAW(2000) typed expression columns

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a 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.

5. Restart the database in NORMAL mode.

   Note:

   The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

6. Run the rdbms/admin/utlrp.sql script to recompile invalid objects. You must be connected AS SYSDBA to run the script.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in a PDB

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a PDB:

1. Shut down the PDB.

2. Reopen the PDB in migrate mode.

   Note:

   The following SQL statement can be used to reopen a PDB in migrate mode when the current container is the PDB:

   ALTER PLUGGABLE DATABASE pdb-name OPEN UPGRADE;

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. Reopen the PDB in NORMAL mode.

   Note:

   The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

6. Run the rdbms/admin/utlrp.sql script in the PDB to recompile invalid objects. You must be connected AS SYSDBA to run the script.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle RAC Database

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in an Oracle RAC database:

1. Shut down all of the Oracle RAC database instances, except one.

2. Restart the Oracle RAC database instance in UPGRADE mode.

3. Change the setting of MAX_STRING_SIZE to EXTENDED.

4. Run the rdbms/admin/utl32k.sql script in the Oracle RAC database instance. You must be connected AS SYSDBA to run the script.

5. Restart all Oracle RAC database instances in NORMAL mode.

   Note:

   The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

6. Run the rdbms/admin/utlrp.sql script to recompile invalid objects. You must be connected AS SYSDBA to run the script.

Increasing the Maximum Size of VARCHAR2, NVARCHAR2, and RAW Columns in an Oracle Data Guard Logical Standby Database

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in an Oracle Data Guard logical standby database:

1. Shut down the Oracle Data Guard primary database and logical standby database.

2. Restart the primary database and logical standby database in UPGRADE mode.

3. Change the setting of MAX_STRING_SIZE to EXTENDED on the primary database and logical standby database.

4. Run the rdbms/admin/utl32k.sql script on both the primary database and the logical standby database. You must be connected AS SYSDBA to run the script.

5. Restart the primary database and logical standby database in NORMAL mode.

   Note:

   The utl32k.sql script increases the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns for the views where this is required. The script does not increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns in some views because of the way the SQL for those views is written.

6. Run the rdbms/admin/utlrp.sql script on the primary database and logical standby database to recompile invalid objects. You must be connected AS SYSDBA to run the script.

7. Restart SQL Apply.