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 CDB

To increase the maximum size of VARCHAR2, NVARCHAR2, and RAW columns in a CDB and in all the PDBs in the CDB:

1. Connect to the CDB AS SYSDBA.

2. In the root, change the setting of MAX_STRING_SIZE to EXTENDED:

   ALTER SESSION SET CONTAINER=CDB$ROOT;
   ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE;

   Note:

   The root continues to use STANDARD semantics even after MAX_STRING_SIZE is set to EXTENDED. The reason for setting MAX_STRING_SIZE to EXTENDED in the root is so all the PDBs in the CDB can inherit the EXTENDED setting from the root.

3. Shut down the CDB.

4. Restart the CDB in UPGRADE mode.

   startup upgrade;

5. Use the catcon.pl script to run the rdbms/admin/utl32k.sql script in the root and in all the PDBs in the CDB to increase the maximum size of the VARCHAR2, NVARCHAR2, and RAW columns. The --force_pdb_mode ‘UPGRADE’ option is used to ensure that all PDBs, including application root clones, are opened in migrate mode. Enter the SYS password when prompted:

   $ cd $ORACLE_HOME/rdbms/admin
   $ mkdir /scratch/mydir/utl32k_cdb_pdbs_output
   $ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utl32k_cdb_pdbs_output' -b 
   utl32k_cdb_pdbs_output utl32k.sql
   catcon: ALL catcon-related output will be written to [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_catcon_23172.lst]
   catcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output*.log] files for output generated by scripts
   catcon: See [/scratch/mydir/utl32k_cdb_pdbs_output/utl32k_cdb_pdbs_output_*.lst] files for spool files, if any
   Enter Password: 
   catcon.pl: completed successfully
   $

   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. Connect to the CDB AS SYSDBA and shut down the database.

7. Restart the CDB in NORMAL mode.

   startup;

8. Use the catcon.pl script to run the rdbms/admin/utlrp.sql script to recompile invalid objects in the root and in all the PDBs in the CDB. The --force_pdb_mode ‘READ WRITE’ option is used to ensure that all the PDBs (including application root clones) are opened in read write mode. Enter the SYS password when prompted:

   $ cd $ORACLE_HOME/rdbms/admin
   $ mkdir /scratch/mydir/utlrp_cdb_pdbs_output
   $ $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/scratch/mydir/utlrp_cdb_pdbs_output' -b utlrp_cdb_pdbs_output utlrp.sql
   catcon: ALL catcon-related output will be written to [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_catcon_24271.lst]
   catcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output*.log] files for output generated by scripts
   catcon: See [/scratch/mydir/utlrp_cdb_pdbs_output/utlrp_cdb_pdbs_output_*.lst] files for spool files, if any
   Enter Password: 
   catcon.pl: completed successfully
   $

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.