Oracle Identity Governance (OIG) 12.2.1.3 Modify ‘max_string_size’ Parameter To "EXTENDED" for Oracle Database 12.1.0.2 (Doc ID 2592007.1)

Last updated on NOVEMBER 15, 2022

Applies to:

Purpose

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

From Oracle Database 12c onwards, table column(s) length can be modified to greater than 4000 characters when this parameter value is set to EXTENDED.

Allowed values for this parameter are:

1. STANDARD

   1. Default value.

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

2. EXTENDED

   1. Means that the 32767 byte limit introduced in Oracle Database 12c applies.

Important considerations before modifying parameter to EXTENDED:

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

2. Cannot change the value of MAX_STRING_SIZE from EXTENDED to STANDARD.

• The only way to revert is to restore the database from backup prior to running the conversion script, $ORACLE_HOME/rdbms/admin/utl32k.sql

3. Database restart is required several times during modification process.

Known ORA- exceptions encountered when modifying the table column(s) length to greater than 4000 characters are:

1. ORA-30556: either functional or bitmap join index is defined on the column to be modified

• This is expected behavior.

• Cannot alter length for columns part of functional or bitmap index.

2. ORA-01404: ALTER COLUMN will make an index too large

• This is expected behavior.

• When a column length is increased, indexes on this column may no longer fulfil the maximum index key size constraint, which is based on the index tablespace block size.

3. ORA-01450: maximum key length (6398) exceeded

• This is expected behavior.

• When creating an index, the total length of the index cannot exceed a certain value. This value depends primarily on the DB_BLOCK_SIZE.

Following types of function based indexes are not supported when database parameter ‘max_string_size’ is set to EXTENDED:

Scope

Details

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.